1 / 29

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

krysta
Download Presentation

Database Design & Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Database Design Database Design & Management

  2. 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)

  3. 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

  4. 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

  5. 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?

  6. 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

  7. 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?

  8. 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

  9. 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

  10. 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?

  11. 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?

  12. 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?

  13. 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?

  14. 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

  15. 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?

  16. 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?

  17. 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?

  18. 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?

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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

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

  29. 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

More Related