Database Design with MySQL

1
Database Design with MySQL

• Contents
• What is a Data? What is Database?
• What is Database Management System?
• Problems without Relational Database?
• Basic Rules of Relational Databases.
• Reason for the rules
• Primary and Foreign Keys
• Anomalies in a Relation
• Normalization
• Relationships between Tables
• Referential Integrity

2
What is Data?

• Data is any information we want to keep track of.
• Important telephone numbers.
• A baseball card collection
• A box full of tax receipts

3
What is a Database?

• A database is any organized collection of
  information.
• Some examples of databases you may encounter in
  your
• daily life are
• a telephone book
• T.V. Guide
• airline reservation system
• motor vehicle registration records
• papers in your filing cabinet
• files on your computer hard drive.
• Each record in a database is composed of
  important
• elements of information for a particular item.
• Each record is composed of a set of fields which
  contain
• the individual elements of information.

4
What is a Database? Contd.
DataBase ( a collection of records)
Records(a collection of fields)
Fields(individual elements of information)
5
What is Database Management System?

• Stores and Retrieves information in a Database
  (i.e. - An Office Clerk).
• A DBMS Represents Reality, the information should
  be modeled after real life.
• In a baseball card collection the information on
  one card is for one player.
• The cards are Grouped by Teams.
• The Teams are Grouped in Leagues (NL AL)

6
Problems without Relational Database?

• Redundant data leads to inconsistent data.
• The more times you enter the same data the more
  likely you are to make a mistake.
• If the data changes (new address) you have to
  change the data in many places.

7
Basic Rules of Relational Databases.

• Relational Database Contains more than one table.
• Tables are logical groupings of similar data.
• Tables consist of Rows (records) and Columns
  (fields).
• All the columns in a table are RELATED to the key
  FIELD.

8
Reason

• Most Applications have multiple related tables to
  present the information in an efficient manner.
• Multiple Tables can manipulate data more
  efficiently.
• If You have multiple tables you need Unique
  Primary Keys, and Foreign Keys.

9
Primary and Foreign Keys

• You use Primary and Foreign Keys to develop
  relationships between tables.
• Primary Key in Table A must be Unique
• Primary Key in Table A relates to a field in
  table B.
• Foreign Key in Table B wont be unique, except in
  ONE-TO-ONE Relationships.

10
Primary and Foreign Keys Example
11
Anomalies in a Relation

• A table that meets minimum definition of a
  relation may not have an effective structure.
• An anomaly is a weakness in the way a relation is
  set up.
• Consider the following table
• This table has all three anomalies insert,
  update, and delete.

12
Normalization

• Normalization Rules provide the mechanism for the
  decomposition of rules to avoid anomalies
• The various forms of Normalization from First to
  Fourth convert complex data structures into
  simple, stable data structures
• Normalization Forms higher than Fourth are not
  useful enough and not considered in this course

13
First Normal Form (1NF)

• A relation is said to be in First Normal Form if
  there is no attribute value occurring as a set of
  values are repeating groups. ExampleSTUDENT
  (Student-Id, Name, Major, Course1-Id,
  Course1-Name, Course2-Id, Course2-Name...)
• To put the above table in 1NF, extend the course
  data downward into multiple rowsSTUDENT
  (Student-Id, Name, Major, Course-Id, Course-Name,
  Grade)

14
Second Normal Form (2NF)

• A relation is said to be in 2NF if no non-key
  attribute is functionally dependent on just part
  of the key
• Relations with single attribute keys (i.e. not
  composite keys) are already in 2NF
• Decomposition method is used to convert to 2NF
• STUDENT (Student-Id, Student-Name, Major)COURSE
  (Course-Id, Course-Name, Instructor,
  Office)REGISTRATION (Student-Id, Course-Id,
  Grade)

15
Third Normal Form (3NF)

• A transitive dependency occurs when a non-key
  attribute is dependent on one or more other
  non-key attributes A gt B, A gt C, and B gt C
  where A is the key, B and C are non-key
  attributes
• A relation is in 3NF if it is already in 2NF and
  there are no transitive dependencies
• In the table COURSE (Course-Id, Course-Name,
  Instructor, Office), Instructor gt Office is
  transitive dependency change toCOURSE
  (Course-Id, Course-Name, Instructor)INSTRUCTOR
  (Instructor-Name, Office)

16
Relationships

• Four Basic Relationships Between Tables
• One - To One
• One - To - Many
• Many - To - One
• Many - To - Many

17
Relationships

• Four Basic Relationships Between Tables
• One To One
• when the Primary Key is the same in both tables.
• One To Many
• When the Primary Key is Unique in each table
• Many To Many
• Involves a Pair of One - To - Many Relationships.
• Table A has a One - To - Many Relationship with
  Table B, Table C has a One - To - Many
  Relationship with Table B, Table A has a Many -
  To - Many Relationship with Table C.

18
Referential Integrity

• RI defines a reference from one table to another
  table to guarantee that the data in the related
  table is valid.
• Declarative Referential Integrity (DRI)
• When you define a relationship between two tables
  you can declare RI be enforced, or turn it off.
• Trigger
• A piece of code that is called every time a
  record change is posted to a table.