Database Design & Management

Database Design Database Design & Management

Representing Entities with the Relational Model • Define a table for each entity • Give the table the same name as the entity • Make the primary key the same as the identifier of the entity • May require the addition of a surrogate key • Create a column for each attribute in the entity • Apply the normalization process to remove any normalization problems (remember we did not worry about normalization issues when we created our data models)

Column Properties • Each column in a table has specific properties • Data type • Null status • Default value • Constraints • Column properties are usually set when the table is initially created

Column Data Types • What is a data type? • Why is it important for the DBMS to know the data type of a column? • Give examples of data types • Each DBMS supports its own set of unique data types • Look at the documentation for your particular DBMS

Column Null Status • Null Status of a column • Determines if a value must be entered when the row containing the column is created • If the column MUST have a value, then use NOT NULL • If the column can be left empty, then use NULL • The Null status: NULL or NOT NULL • Is set when the table is created • Are there any problems you could run into if you incorrectly set the Null status? • For example, if you use NOT NULL when you really meant NULL? • Or you used NULL when you meant NOT NULL?

Column Default Values • What is a default value? • The DBMS will automatically set the value in a column to the default value, if one exists, when the new row is created

Data Constraints • What is a data constraint? • A restriction on the values contained in a particular column • What fields (columns) can you think of that may benefit from data constraints?

Verifying Normalization • Last step • Need to verify that your tables are normalized. Why? • In the design phase we have not yet formally gone through the normalization process

Denormalization • Given CUSTOMER(ID, Street, City, State, Zipcode) • Should we break out Zip code into its own table? • Does the functional dependency Zipcode State exist? • Then by following the rules for normalization, we should create a Zipcode table and place the Zipcode as a foreign key in the CUSTOMER table • Any concerns about this? • How do you get the person’s address? • Two tables must be read • Programmers will not be happy campers! • We usually think of the address as street, city, state and zip: all in one group • Therefore, we leave the Zip code in the CUSTOMER table and remove the new Zip table • This is called Denormalization

Consequences of Denormalization • What happens if we denormalize the Zip code? • i.e. Zip code is still in the Customer table • Consider the 3 basics operations • Insert • What happens if you want to insert a new zip code? • Is this ok? • Update • What happens if a zip code changes? • Is this ok? • Delete • What happens if you delete the only customer with a particular zip code? • Is this ok? • What is your conclusion about denormalizing Zip code?

Representing Weak Entities • The previous process for creating relational tables from entities works for all entities • However, weak entities sometimes require special attention • What is a weak entity? • An entity that logically depends on another entity • Look at Figure 5-8, page 266 • Are there any weak entities in this diagram? • What about Commision_Check? What type of entity is this? Why?

Representing Weak Entities • If a weak entity is NOT ID-Dependent (what does this mean?) • It can be represented as a table using the previous process • What should happen if the parent of a non-id dependent weak entity is deleted? • If a weak entity is ID-Dependent (what does this mean?) • For example, SALES_COMMISSION on SALESPERSON • Figure 5-8, page 266 • The identifier of the parent and the weak entity both appear in the table for the weak entity • What would be the primary key for SALES_COMMISSION? Why? • Why not just CommissionPeriod?

Representing Weak Entities • See Figure 5-9 (a), page 267 • Describe in English what the E-R diagram “says” • Is there a weak entity in this diagram? • How do you know? • Is it ID-Dependent or not ID-Dependent? Why? • Notice the primary key of LINE_ITEM in (b) • What would happen if InvoiceNumber was not included in the primary key for LINE_ITEM?

Representing Relationships • So far we have created a relational design for the entities in an E-R diagram by translating these entities into tables • Now we need to create relationships among these tables • Techniques to do this depend on the maximum cardinality of the relationships • What is meant by maximum cardinality? • 3 relationship possibilities exist • One-to-one (1:1) • One-to-many (1:N) • Many-to-many (M:N) • In general, we create relationships by placing foreign keys in tables

Relationships Among Strong Entities • Representing 1:1 Strong Entity Relationships • What is a 1:1 relationship? What does it mean? • See Figure 5-10 (a), page 270 • What does this diagram “say” in English? • Simply place the primary key of one of the tables into the other table as a foreign key • See Figure 5-10 (b) • See Figure 5-10 (c) • What is the difference? Does it matter? Why or why not?

Relationships Among Strong Entities • Representing 1:N Strong Entity Relationships • What is a 1:N relationship? What does it mean? • See Figure 5-12 (a), page 272 • What does this diagram “say”? • In a 1:N relationship, the terms parent and child are sometimes used • The parent refers to the 1 part of the relationship • The child refers to the N (many) part of the relationship • In Figure 5-12 (a), what is the parent? The child?

Relationships Among Strong Entities • To represent a 1:N strong entity relationship, simply place the primary key of the parent entity into the table representing the child entity as a foreign key • See Figure 5-12 (b) • Don’t forget the referential constraint • ItemNumber in QUOTATION must exist in ItemNumber in ITEM • What would happen if you placed the child key into the parent entity as a foreign key? Would this work also? Why or why not?

Relationships Among Strong Entities • Representing M:N Strong Entity Relationships • What is an M:N relationship? What does it mean? • See Figure 5-13 (a), page 273 • What does this diagram “say”? • Let’s try to use our previous approach with foreign keys • What happens if we try to place the student identifier (SID) into the CLASS table as a foreign key? • What happens if we try to place the class identifier (ClassNumber) into the STUDENT table as a foreign key?

Relationships Among Strong Entities • Let’s try another approach • See Figure 5-14, page 274 • We add a row for each student enrolled in a class into the CLASS table • So we have two records for Class 10 and Class 30, for example • This means that two students have enrolled in Class 10 and two students have enrolled in Class 30 • Any problems with this approach? • What about if student 300 drops out of class 40? • We have just lost this class • This approach does not work

Intersection/Junction Tables • The solution is to create a third table, called an intersection table (or sometimes a junction table) • This new table represents the relationship itself • Therefore it contains the primary key from each of the two original tables • This new table now contains two foreign keys, which together form its composite primary key STUDENT (SID, StudentName, Phone, EmailAddress) CLASS (ClassNumber, ClassTime, ClassName, Description) STUDENT_CLASS (SID, ClassNumber) Where SID in STUDENT_CLASS must exist in SID in STUDENT ClassNumber in STUDENT_CLASS must exist in ClassNumber in CLASS

Intersection/Junction Tables • See Figure 5-15 (a), page 274 • What does this diagram “say”? • We have taken an M:N relationship and decomposed it into two 1:N relationships • The key for an intersection table is ALWAYS the combination of parent keys • Both parent key values are required for each intersection table row • Contains ONLY the columns that make up its composite key • Stay tuned… • STUDENT_CLASS is an ID-Dependent weak entity on both STUDENT and CLASS • We started with an M:N strong entity relationship and in order to create a database design, we had to create an ID-Dependent weak entity

Relationships Using Weak Entities • Now, after I just said that all intersection tables contains ONLY the columns that make up its composite key…. • Another type of ID-Dependent weak entity occurs when we take an intersection table and add entity attribute(s) (table columns) beyond those of the composite primary key • See Figure 5-18, page 277 • The STUDENT_CLASS entity now contains data uniquely its own • This entity is now called an association entity and the relationship is called an association relationship

Relationships with Subtypes • The identifier of a subtype entity is the identifier of the associated supertype entity • Huh? What does this mean? • The identifier of the subtype becomes the primary key of the subtype and the foreign key linking the subtype to the supertype • See Figure 5-20 (a) and (b), page 278

Representing Recursive Relationships • A recursive relationship is a relationship among entities of the same class • There relationships can be represented using the same techniques we have already used • 3 types of recursive relationships exist • 1:1 • 1:N • N:M • See Figure 5-21, page 279 for an example of each • What do each of these diagrams “say”? • Know that these types of relationships exist, how to describe them and how to recognize them

Summary • To transform an E-R data model into a relational database design • Create a table for each entity • Attributes of the entity become the columns of the table • Identifier of the entity becomes the primary key of the table • For each column • Define data types, null status, any default values, any data constraints • Apply the normalization process to the table • Creating additional tables if necessary • Use denormalization if necessary • Keep in mind that denormalization will introduce insert, update and delete problems

Summary • Weak entities are represented by a table • Supertypes and subtypes are each represented by separate tables • The E-R model has 3 types of relationships • 1:1 • 1:N • N:M

Summary • The M:N relationship requires the adding of a third table • Called the intersection or junction table • This new table represents the relationship itself • This new table now contains two foreign keys, which together form its composite primary key • Recursive relationships are relationships in which the participants in the relationship arise from the same entity class

For February 19th • Review for Exam #1 • Project #7 is due • No quiz • Exam #1 is on February 24th • See next slide

Exam 1, February 24th • Exam #1 • If you do not attend class, I will place a make-up exam in the test center by 9:30 Tuesday morning • NOTE: I will subtract 20 points from your test score • This is your responsibility! I will not send emails or phone you or visit your favorite haunts to let you know that you need to take the exam… • You will have until 12:00 p.m. (noon) on Wednesday, February 26th to complete the exam in the test center • I will pick up any completed exams at 12:00 on Wednesday so I can have them graded for class on Wednesday • If you do not complete the exam, you will earn 0 points for this exam score

Database Design & Management