1 / 91

Database Design

Database Design. Database Design. Database Design. We Will Consider 3 general Topics Here:. Transforming (Mapping) ERDs into Relations. How to Transfer your ERDs into Tabular form. Normalization. How to Maximize your table utility. General Design Considerations.

renee
Télécharger la présentation

Database Design

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

  2. Database Design We Will Consider 3 general Topics Here: • Transforming (Mapping) ERDs into Relations • How to Transfer your ERDs into Tabular form • Normalization • How to Maximize your table utility • General Design Considerations • Other Design Considerations

  3. ERDs to RDBMS Transforming ERDs to the RDBMS • Transforming (Mapping) ERDs into Relations is relatively Straightforward • Most CASE tools can automatically perform the transformation --- HOWEVER --- • Many CASE tools can NOT transform certain complex relationships (e.g, Ternary) • There often many valid alternatives, and the designer must choose the best one • The designer must be prepared to evaluate the correctness of the CASE generated solution

  4. Supervisor Name PhysID Zipcode State Specialty City Street Supervisor ERDs to RDBMS Step 1: Map Regular Entities Given a simple table with no composite/complex attributes: Physician PhysID Name Specialty Street City State ZipCode A Direct Mapping

  5. ERDs to RDBMS The SQL Commands Needed would be: Physician Table Attributes/Fields CREATE TABLE physician ( physid CHAR(9), PhysID Name name CHAR(30), specialty CHAR(20), Specialty Street street CHAR(25), city CHAR(20), City State state CHAR(2), zipcode CHAR(5), Zipcode Supervisor supervisor CHAR(9), PRIMARY KEY (physid), FOREIGN KEY (supervisor) REFERENCES TABLE physician (physid));

  6. Address PhysID ZipCode Name Street City State PatID PhysID ERDs to RDBMS Some Composite attributes can also be easily decomposed and Mapped: Patient Name Street City State ZipCode PatID

  7. ERDs to RDBMS The SQL Commands Needed would be: Patient Table Attributes/Fields CREATE TABLE patient ( patid CHAR(9), PatID Name name CHAR(30), street CHAR(25), Street City city CHAR(20), State state CHAR(2), Zipcode zipcode CHAR(5), Physid physid CHAR(9), PRIMARY KEY (patid), FOREIGN KEY (physid) REFERENCES TABLE physician (physid));

  8. Name PhysID Street City State Zipcode Specialty ERDs to RDBMS Multi-valued Attributes also may need decomposition Suppose that a Physician could have multiple specialties: Physician (We have omitted the field supervisor only to save space) How Many Specialties does a Physician have ?? One?? Two?? Twenty??

  9. Name PhysID Specialty Zipcode State City Street ERDs to RDBMS We already know the problems associated with such a problem. We must allocate enough field to meet the Maximum Case. Physician PhysID Name Specialty1 • • • Specialtyn Street City State ZipCode The same problem we had when we put PhysID in Patient

  10. ERDs to RDBMS We need to decompose the multi-valued attributes into separate relations, but, it isn’t really too difficult It DOES depend on how we state the problem, however “A Physician many have Many specialties.” “A specialty can only have one Physician .” (A 1:M Situation) --- OR --- “A Physician many have Many specialties.” “A specialty may be shared by many Physicians.” (A M:M Situation)

  11. Has ERDs to RDBMS In a 1:M Physician - Specialty relationship: Physician Specialty The primary key for relation Specialty will be the concatenated Key PhysID, SpecCode(which is unique) CREATE TABLE physician ( physid CHAR(9), name CHAR(30), street CHAR(25), city CHAR(20), state CHAR(2), zipcode CHAR(5), supervisor CHAR(9), PRIMARY KEY (physid), FOREIGN KEY (supervisor) REFERENCES TABLE physician (physid)); CREATE TABLE specialty ( physid CHAR(9), specCode CHAR(10), Specname CHAR(20), PRIMARY KEY (physid, specCode), FOREIGN KEY (physid) REFERENCES TABLE physician (physid)); NOTE: There is no field specialty

  12. Desc. Name PhysID SpecCode PhysID Street City State Zipcode Has PhysID ERDs to RDBMS Looking at the relations and their Mapping: Physician Specialty PhysID Name Street City State ZipCode SpecCode Desc

  13. Name SpecCode. PhysID SpecCode Desc. PhysID Zipcode State Street City Has ERDs to RDBMS What if the Relationship is M:M ??? Physician Specialty The Relationship will become an Associative Entity (More on that later)

  14. Has Dependents Name PhysID DepName Gender DOB ERDs to RDBMS Step 2: Map Weak Entities Recall that a weak entity is one which exists only through an identifying relationship with another entity Consider the following relationship Physician Where Dependents would not exist without Physician (the owner of Dependents)

  15. Has Dependents Gender DepName PhysID PhysID Name DOB PhysID ERDs to RDBMS Notice that at this point in time there is no way of associating the two relations (i.e., no common key) By now, however, we know to put the foreign key in a 1:M relationship on the side of the many Physician PhysID Name DepName DOB Gender

  16. ERDs to RDBMS The SQL Commands Needed would be: Dependents Table Attributes/Fields CREATE TABLE dependents ( depname CHAR(30), depname physID physID CHAR(9), DOB DATE, DOB gender gender CHAR(1), PRIMARY KEY (depname), FOREIGN KEY (physid) REFERENCES TABLE physician (physid));

  17. Has Dependents PhysID2 Gender DepName PhysID PhysID1 Name DOB ERDs to RDBMS Weak Relationships can be more complex, however Suppose that two Physicians can be married to each other (and hence, both have the same dependents) There are a few ways we could deal with this, but let’s take one (NOT necessarily the best) Physician PhysID Name DepName PhysID1 PhysID2 DOB Gender

  18. Has Dependents DepName Name PhysID DepName Gender PhysID DOB PhysID DepName ERDs to RDBMS How Else Could this be done ??? Since this is now a M:M relationship, the relationship Has could become an Associative Entity Physician PhysID Name DepName DOB Gender Again, More on this later

  19. Patient PatID PhysID Treats Name PhysID Name ZipCode Street City State PhysID ERDs to RDBMS Step 3: Map Binary Relationships Essentially, we have already done most of these Binary 1:M Relationships: Physician PhysID Name PatID Name Street City State ZipCode We’ve already seen the SQL Code Needed

  20. Suffer Illness PatID Name PhysID Address Others IllCode Name Others Treatment PatID IllCode PhysID ERDs to RDBMS Binary M:M Relationships: Recall our previous example: Patient Date Time ••• PatID Name ••• IllCode Name •••

  21. ERDs to RDBMS The SQL Commands Needed are: Illness Table Attributes/Fields CREATE TABLE illness Illcode ( illcode CHAR(9), Name name CHAR(30), PRIMARY KEY (illcode)); Suffers Table Attributes/Fields CREATE TABLE suffers patID Patient ( patID CHAR(9), illcode illcode CHAR(9), DateTime DateTime DATE, PRIMARY KEY (patid, illcode, DateTime), FOREIGN KEY (patID) REFERENCES TABLE patient (patID)); FOREIGN KEY (illcode) REFERENCES TABLE illness (illcode));

  22. ERDs to RDBMS Binary 1:1 Relationships: Binary 1:1 Relationships ?? Like a person and their Social Security Number ?? Why Binary ?? Why Not put it in one Table ?? Usually, Yes. Sometimes, 1:1 Binary relationships are very useful Situation 1: Storage Savings Consider the field State (which we have used quite often) The Longest State Name is Massachusetts (13-Bytes of storage; 14, if we include the NULL Character)

  23. PatID Name ••• StateNumber ••• State_No StateName ERDs to RDBMS Given that there are only 50 states, we could enter a state number in the main database (which requires only 1-byte), and link it to a database which stored the entire state name 1-byte of storage per record 1-byte of storage per record 14-bytes of storage per record It Seems like we need 1 additional byte of storage per record this way. Where is the Savings ??

  24. ERDs to RDBMS Suppose that our Patient database had 5,000 records: If we did not have a separate database, we would need: 5,000 * 14-Bytes = 70,000 Bytes of storage If two databases, we need: 5,000 * 1-Bytes = 5,000 Bytes of storage + 50 * 14-Bytes + 50 * 1-Byte = 750 Bytes of storage 5,750 Bytes of storage If there were 1,000,000 records that used state as an attribute (not unrealistic), then our savings would be: If in 1 database: 1,000,000 * 14-Bytes = 14,000,000 Bytes of storage 1,000,000 * 1-Byte = 1,000,000 Bytes of storage If in 2 databases: + 50 * 14-Bytes + 50 * 1-Byte = 750 Bytes of storage 1,000,750 Bytes of storage A savings of 13,000,250 Bytes

  25. State_No ERDs to RDBMS The SQL Commands Needed are: Patient Table Attributes/Fields CREATE TABLE patient PatID ( patID CHAR(9), Name name CHAR(30), State state INTEGER, PRIMARY KEY (patID), FOREIGN KEY (state) REFERENCES TABLE state (State_No)); State Table Attributes/Fields CREATE TABLE state ( state_no INTEGER, StateName Statename CHAR(14), PRIMARY KEY (state_no));

  26. ERDs to RDBMS How can we refer to a table that doesn’t yet exist ??? (Table patient refers to table state, which doesn’t yet exist) We Can’t ! We Need to Reverse the order of Table creation State Table Attributes/Fields CREATE TABLE state State_No ( State_no INTEGER, StateName CHAR(14), StateName PRIMARY KEY (state_no)); Patient Table Attributes/Fields CREATE TABLE patient PatID ( patID CHAR(9), name name CHAR(30), State state INTEGER, PRIMARY KEY (patID )); FOREIGN KEY (state) REFERENCES TABLE state (State_No));

  27. Have ERDs to RDBMS This Does NOT appear to be a 1:1 relationship !!! Absolutely True !! The Relationship is actually: Patient State (A M:1 Relationship) How Can this Be ??? Stuff Happens !!! (ALWAYS be ready to change your ERD AND your thinking)

  28. Assigned ERDs to RDBMS Situation 2: Other Binary 1:1 Relationships: Suppose that each hospital clinic had one physician assigned to be in charge of the clinic. No Physician is assigned to more than 1 clinic Physician Clinic Given 1 Physician, how many Clinics ?? Given 1 Clinic, how many Physicians ?? One One Mandatory?? Mandatory?? No Yes

  29. Assigned Name PhysID ClinID Name PhysID PhysID ERDs to RDBMS Mapping Binary 1:1 Relationships: Physician Clinic PhysID Name ClinID Name

  30. ERDs to RDBMS The SQL Commands Needed are: Clinic Table Attributes/Fields CREATE TABLE clinic ClinID ( ClinID CHAR(9), Name name CHAR(31), PhysID physID CHAR(9), PRIMARY KEY (ClinID)); FOREIGN KEY (physID) REFERENCES TABLE physician (physID)); For a total of 79 Bytes (We Have Already Defined Table physician)

  31. ERDs to RDBMS How Is This Helpful ???? Suppose we had included all the attributes in one Table: Patient Table Attributes/Fields CREATE TABLE patient PatID ( patid CHAR(9), name CHAR(30), Name Clinic clinic CHAR(31), PRIMARY KEY (patid)); In other words, Each record requires 70 Bytes

  32. ERDs to RDBMS Suppose that there were 2,000 Physicians in the database: We would need: 2,000 * 70-Bytes = 140,000 Bytes of storage Assume that only 75 of those physicians were in charge of a clinic. If there were 2 tables: Physician Table: 2,000 * 48-Bytes = 96,000 Bytes of storage Clinic Table: 75 * 79-Bytes = 5,925 Bytes of storage 101,920 Bytes of storage A Savings of 38,080 Bytes (Remember, This is a Simplistic Example)

  33. PatID Suffer Illness Name Name Address PhysID IllCode Others Others PhysID Other Addr Other Treatment PatID IllCode TDate ERDs to RDBMS Step 4: Map Associative Entities (M:M) Recall the Relationship Again, we have already Done this. Patient PatID Name IllCode Name

  34. ERDs to RDBMS The SQL Commands Needed are: Suffer Table Attributes/Fields CREATE TABLE suffer PatID ( PatID CHAR(9), illcode illcode CHAR(9), TDATE TDate DATE, PRIMARY KEY (PatID, illcode, TDate), FOREIGN KEY (PatID ) REFERENCES TABLE patient (PatID), FOREIGN KEY (illcode ) REFERENCES TABLE illness (illcode));

  35. Supervises PhysID Name Supervisor ERDs to RDBMS Step 5: Map Unary Relations Mapping Unary 1:M Relationships: Recall the Relationship Physician PhysID Supervisor Name

  36. ERDs to RDBMS The SQL Commands Needed are: Patient Table Attributes/Fields CREATE TABLE patient PatID ( PatID CHAR(9), name name CHAR(30), Supervisor Supervisor CHAR(9), PRIMARY KEY (PatID), FOREIGN KEY (Supervisor ) REFERENCES TABLE patient (PatID));

  37. Consists of ERDs to RDBMS Mapping Unary M:M Relationships Consider a Part in inventory • A Part may not require any components (e.g., a screw) • A Part mayconsistof multiple parts (e.g., a motor) We Might set up the ERD as: Parts A Many to Many (M:M) Relationship

  38. ERDs to RDBMS But, if there is a M:M relationship don’t we run into the same problem as we did when we first tried to relate Patient with Illness ?? (i.e., where do we put the foreign key ???) • If we put PatID in Illness, we need multiple fields • If we put IllCode in Patient, we still need multiple fields The Solution is Also the same: Decompose the Relationship into 2 tables All we need to do is identify the appropriate attributes

  39. PartNo Component Name CompNo Unit Cost PartNo. PartNo CompNo ERDs to RDBMS Consider the Following attributes/Mapping: Let’s Call the Table: Component Parts PartNo Name UnitCost

  40. ERDs to RDBMS The SQL Commands Needed are: Part Table Attributes/Fields CREATE TABLE part partno ( partno CHAR(9), Name name CHAR(30), unitcost unitcost DECIMAL(7,2), PRIMARY KEY (partno)); Component Table Attributes/Fields CREATE TABLE component partno ( partno CHAR(9), compno Compno CHAR(9), PRIMARY KEY (partno, compno), FOREIGN KEY (partno) REFERENCES TABLE part (partno), FOREIGN KEY (compno) REFERENCES TABLE part (partno));

  41. Time/Date DrugCode Name Name IllCode Address Others IllCode PatID PatID Others Illness PhysID Treatment DrugCode Others ERDs to RDBMS Step 6: Map Ternary (and n-ary) relationships Once again, we should already be familiar with this Recall the Relationship: Patient Prescription

  42. ERDs to RDBMS The SQL Commands Needed are: Treatment Table Attributes/Fields CREATE TABLE treatment patid ( patid CHAR(9), illCode illCode CHAR(9), drugcode drugcode CHAR(9), TDate TDate DATE, PRIMARY KEY (patid, illCode, drugcode, TDate), FOREIGN KEY (patid) REFERENCES TABLE patient (patid), FOREIGN KEY (illcode) REFERENCES TABLE illness (illcode), FOREIGN KEY (drugcode) REFERENCES TABLE prescription (drugcode));

  43. Time/Date DrugCode Name Name IllCode Others IllCode PatID PatID Others Illness PhysID Treatment PhysID Other Other DrugCode Name IllCode Name PatID Name DrugCode Others PatID DrugCode IllCode Date/Time ERDs to RDBMS The Final Mapping is: Patient Prescription

  44. ERDs to RDBMS Summary Mapping ERDs into a Relational Database is generally straightforward ---- BUT ---- ONLY if the ERD is well-conceived

  45. Normalization Normalization Why Normalization ?? Domain Constraints • Assuring that there are no illegal values within a domain (set) of attributes Entity Integrity • Assuring that Every relation has a primary key • Assuring that every primary key is valid • Assuring that every primary key is NOT NULL

  46. Normalization Normalization is the formal process of determining which attributes should be grouped together in a relation • A method to validate the logical design (e.g., ERD) • A Tool to improve the logical design • Satisfaction of certain constraints • Avoidance of unnecessary data duplications • The process of Decomposing relations with anomalies to produce smaller, well-structured relations

  47. Normalization (Continued) Why Normalization ?? Referential Integrity Constraint • Assuring that consistency between relational rows is maintained • Basic Rule Stating • If a foreign key exists in one relation, it MUST be • A Primary Key in another relation, OR • NULL

  48. Normalization Normalization Steps First Normal Form (1NF) • Removal of repeating groups (multi-valued attributes) so that there is a single value at the intersection of each row and column Remember the first problem we encountered in our ERD example: “Each patient can have only one primary physician.” Our first solution to this was not a very good one: • Put PatID in the Physician database

  49. PhysID Name PatID1 PatID2 PatID3 • • • PatIDN 5 6 17 Smith, Mary Smith, John Bush, Georgia A088 B013 F789 . F901 G019 . A187 . • • • • • • • • • PhysID Name Patient 5 Bush, Georgia B013 F901 A187 6 Smith, John F789 17 Smith, Mary A088 G019 Normalization The lay-out (for some of the attributes) then became: Or perhaps (in tabular lay-out):

  50. PhysID Name PatID 5 17 5 17 5 6 Bush, Georgia Bush, Georgia Bush, Georgia Smith, Mary Smith, Mary Smith, John F789 F901 A088 B013 G019 A187 Normalization One solution, which we did not come up with before, is: NOTE: PatID does NOT repeat in a record (There are NO Repeating Groups)

More Related