1 / 15

- Questions - “Null values” - Bonus Exercise X Picture on board

- Questions - “Null values” - Bonus Exercise X Picture on board Eliminate the word ‘potential’ from your database design vocabulary A PC discussion and 1 – 1 relationships Agreements: Picture on board. Relational Database

alexis-long
Download Presentation

- Questions - “Null values” - Bonus Exercise X Picture on board

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. - Questions - “Null values” - Bonus Exercise X Picture on board • Eliminate the word ‘potential’ from your database design vocabulary A PC discussion and 1 – 1 relationships Agreements: Picture on board

  2. Relational Database A database that consists of tables and relationships that adhere to a set of rules and provides • data integrity at the field, record, table, relationship and business levels How do we do that? Each one? Record – primary key major tool (why) Field – data type, helper table, …

  3. Relational Database A database that consists of tables and relationships that adhere to a set of rules and provides Data consistency and accuracy How do we do that? Domain restrictions, helper tables, reports, …

  4. Putting database tables into 1st Normal Form, 2nd Normal Form and 3rd Normal Form A database table is in 1st Normal Form if a) Has a primary key b) All fields atomic c) has no repeating fields.

  5. Second Normal Form A table is in 2nd Normal Form if a)  It is in 1st Normal Form b) It has no Partial Dependenciesa ‘partial dependency’ is a non-key field in a table that depends on only part of the primary key.

  6. Third Normal Form A table is in 3rd Normal Form if a)   It is in 2nd Normal Form b)   It contains no Transitive Dependencies Consider an Employee table with empID, fname, lname, spouseSSN, spousefName, spousePhone • Example records • 152 Joe Smith 123456789 Kim 512-332-3313 • 251 Jill Jones   • 414 Sarah Kerns 512314123 Tom 212-412-1424

  7. In this table, fname depends on empID, lname depends on empID, spouseSSN depends on empID, spousefName depends on spouseSSN and spousePhone depends on spouseSSN. The spousefname -> spouseSSN -> seuID dependency is a 'transitive dependency'.

  8. To eliminate a transitive dependency, create a new 'Spouse' table consisting of spouseSSN, spousefName and spousePhone. LEAVE the spouseSSN in the Employee table. spouseSSN in the Employee table is a foreign key. Since we are in Texas instead of Utah, this is a 1-1 relationship

  9. Types of relationships between tables 1-1 1-Many Many-Many In a relational DBMS, M-M relationships are resolved into 2 1-M relationships by means of a bridge table/association table/other names by different authors. We’ll call them bridge tables.

  10. 1. What is the path to your ‘home’ directory on cn01.cs.stedwards.edu: /seu/cs/home/user/e/tturner2 What is the path to your ‘home’ directory on your vm? 2. If you have a crows foot on a PK – TRC 3. Einstein says “…..” You say “….” Explain in your own words. 4. Make sample records. Make sample records

  11. cn01.cs.stedwards.edu db01.cs.stedwards.edu mysql emacs mysql -h …. mysql …. vm133048.cs.stedwards.edu mysql emacs

  12. A method to resolve M:M relationships Create a new table consisting minimally of the primary keys from each of the participating tables. Normalize the bridge table, with the primary key containing at least each of the keys from the other tables. This table becomes a child table to each of the other tables. The other tables become parents. The respective foreign keys are the primary keys of the parents.

  13. Example of M:MStudentClass seuID dept fname number … Title … One student – many classesone class – many students Create table Enrol seuID dept number grade … as needed

  14. Another example - Entity-Relationship Diagram (ERD) Student seuID (PK) (FK) dept (PK) (FK) number (PK) (FK) semestersection Grade … Class Enrol Some sample records: 111 cosc 3337 SP13 01 cosc 3337 DBMS 111 Jill Student 222 cosc 3337 SP13 01 culf 3331 Amer. D 222 Joe Student 111 culf 3331 SP13 03 How about 111 chem 1301 SP13 02 seuID (PK) fname lname … dept(PK) number (PK) Title …

  15. Integrity Notes from http://www.databasedev.co.uk/database_normalization_process.html • There are 4 types of data integrity: • Entity Integrity ensures that each row (record) is a unique instance in a particular table by enforcing the integrity of the primary key or the identifier column(s) of a table (e.g. ID, Reference Code, etc). • Domain Integrity ensures validity of entries (data input) for a column through the data type, the data format and the range of possible values (e.g. date, time, age, etc.). • Referential Integrity preserves the defined relationships between tables when records are added, modified or deleted by ensuring that the key values are consistent across tables; such consistency requires that there are no references to non-existent values and if a key value changes, all references to it change consistently through database, otherwise a key value cannot be changed. • User-Defined Integrityenables specific (required) business rule(s) to be defined and established in order to provide correct and consistent control of an application's data access (e.g. who can have permissions to modify data, how generated reports should look like, which data can be modified, etc.)

More Related