Understanding the Relational Data Model and Database Concepts
Delve into the world of relational data modeling, database integrity rules, and structure. Explore how a university database in MS Access utilizes relational tables and fields to store information. Learn about the key terminology associated with relational databases, such as primary keys, foreign keys, and table orientation. Discover the significance of entity modeling and the various types of entities involved in data storage and management.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Overview Relational model basics Integrity rules Rules about referenced rows 2
University Database A relational database uses a series of tables or files called relations 3
Tables Relational database is a collection of related tables Heading: table name and column names attributes or fields Body: rows, occurrences of data. Each row is a record STUDENT table StdSSN 123-45-6789 124-56-7890 234-56-7890 StdLastName WELLS NORBERT KENDALL StdMajor IS FIN ACCT StdClass FR JR JR StdGPA 3.00 2.70 3.50 5 5
Alternative Terminology Table-oriented (RDBMS) Table Set-oriented (Formal Terms) Relation Record-oriented Record-type, file Row Tuple Record Column Attribute Field No. of rows Cardinality No. of records No. of columns Degree No. of fields Primary key Primary key Record key 6 6
Data Structure Overview Structure all data into tables User sees only tables A table is composed of rows and columns Rows and columns are not sorted in any particular order No duplicate rows A row may not contain a repeating group The total collection of values which may occur within a column is the domain of that column The primary key ensures a row s uniqueness A foreign key maintains the relationship between tables 7
Data Modeling Concepts: Entity Entity a class of persons, places, objects, events, or concepts about which we need to capture and store data. Named by a singular noun Persons: agency, contractor, customer, department, division, employee, instructor, student, supplier. Places: sales region, building, room, branch office, campus. Objects: book, machine, part, product, raw material, software license, software package, tool, vehicle model, vehicle. Events: application, award, cancellation, class, flight, invoice, order, registration, renewal, requisition, reservation, sale, trip. Concepts: account, block of time, bond, course, fund, qualification, stock. 8-8 8
Relational Model The columns of information (called attributes) in a table (a relation) relate only to the primary key for each row. The column is equivalent to a field. The rows (called tuples) are uniquely identified by a primary key and are equivalent to a record The relationship between relations (files/tables) are kept by foreign keys - a primary key of one relation that appears as an attribute of another relation 9 9
Data Modeling Concepts: Foreign Keys Foreign key a primary key of an entity that is used in another entity to identify instances of a relationship. A foreign key is a primary key of one entity that is contributed to (duplicated in) another entity to identify instances of a relationship. A foreign key always matches the primary key in the another entity A foreign key may or may not be unique (generally not) The entity with the foreign key is called the child. The entity with the matching primary key is called the parent. 8-10 10
Data Modeling Concepts: Parent and Child Entities Parent entity - a data entity that contributes one or more attributes to another entity, called the child. In a one-to-many relationship the parent is the entity on the "one" side. Child entity - a data entity that derives one or more attributes from another entity, called the parent. In a one-to-many relationship the child is the entity on the "many" side. 8-11 11
Data Modeling Concepts: Foreign Keys Primary Key Student ID 2144 3122 3843 9844 2837 2293 Last Name Arnold Taylor Simmons Macy Leath Wrench First Name Betty John Lisa Bill Heather Tim Dorm Smith Jones Smith Smith Jones Foreign Key Duplicated from primary key of Dorm entity (not unique in Student entity) Primary Key Dorm Residence Director Smith Andrea Fernandez Jones Daniel Abidjan 8-12 12
Activity Identify the entities and attributes based upon the excerpt from an interview of the manager of an appliance repair shop described on the next several slides. 1) Create the resulting tables. 2) 13 13
Activity (contd) We re in a bit of trouble here. This is the third week in a row that we have lost a customer s TV set. Somehow we ve got to keep better track of who brought in which appliance, and when. And when the appliance is picked up by the customer. We also need to know which of our technicians worked on which appliance, and how long it took. We are also having problems with repair equipment loss. Some equipment has gone missing and I m afraid it is due to employee theft. The equipment is numbered but we can never remember where we put it. We need to have each employee check each piece out when he needs it, and check it back in when he s done with it and only one piece at a time. That way the last guy to check it out pays for it if it s missing. As you can see this is a big shop and each technician is assigned to a bench but I can t keep track of which employee is assigned to which bench. Sometimes I wish we hadn t gotten so big 14
Activity (contd) I had this idea we d stamp each appliance the customer brought in, with a number. Then we d put it on the shelf, and when a technician needed work he d go to the shelf and sign it out. That way, we d know who had it last. If the technician can t fix it, he d put it back and sign it back in. But only after he wrote down how long he had worked on it. Well it was a great idea. I had these cards printed up and everybody wrote all the information on the card and it worked great. Then we ran out of cards and the print shop was closed and the system fell apart. Here s another problem the other day a customer walked in with a whole pile of appliances TVs, radios, a microwave, - you name it. We had to write his name and phone number on 15 of those little slips. And along the same lines there was a customer who came in the same day who got rather agitated when she had to give her name and phone number. She seemed to think we should have remembered it because she was just in a week before. So you can see we have some major problems and we hope you can design a better system for us. 15
Table Rules Columns (attributes) should be non-decomposable (Date and Time are recognized as exceptions) This account number 22-04-6794 Customer number General Ledger Sub-ledger Would be represented in a table as follows: Account General Ledger 22 Sub Ledger 04 Customer Number 6792 16
Table Rules (continued) The order of columns should be arbitrary These are not valid tables because the columns cannot be rearranged without changing the meaning of the data Employee Sales Employee Number FIGURES SALES FOR THE SALES LAST SALES FIVE SALES MONTHS SALES Order ORDER NUMBER ORDER PART# DETAIL QTY (9 OCCU PART# RRENCES) QTY . 17
Table Rules (continued) Note that if the order of columns is not arbitrary, the table will generally have one or more of the following: 1. Implicit rather than explicit data 2. An arbitrary limit to the number of occurrences 3. A maintenance anomaly requiring the rearrangement of data when new data is inserted 4. A retrieval anomaly such that the actual position of values is unpredictable 18
Table Rules (continued) The order of the rows should be arbitrary This is not a valid table because the rows cannot be rearranged without changing the meaning of the data SALARY HISTORY EMPLOYEE NUMBER SALARY AMOUNT Current (employee 1) previous starting 1 175.00 1 150.00 1 140.00 Current (employee 2) previous starting 2 200.00 2 195.00 2 180.00 19
Tables Rules (continued) Each row within a table should be unique This is also an invalid table because the rows may not always be unique EMPLOYEE NUMBER SALARY AMOUNT Current (employee 1) previous starting 1 175.00 1 150.00 1 140.00 2 200.00 Current (employee 2) previous starting 2 195.00 2 180.00 3 110.00 Current (employee 3) previous starting 3 105.00 3 105.00 20
Table Terms Duplicate Values A duplicate value is a value or group of values in a table that exactly equal another value or group of values in the same column(s) Rule: Columns or groups of columns marked ND may not contain duplicate values 21
Table Terms Null Value A null value is a missing entry in a column. Null means unknown or inapplicable and is not the same as blank or zero The equivalence of two null values is indeterminate; that is, two null values are not necessarily equal Rule: Columns marked NN (no nulls ) may not contain null values 22
Table Terms Derived Attribute A derived attribute is computed from other attributes Totals Age computed from Birth-Date Typically should not be stored directly Update of derived attributes creates overhead and puts responsibility on coders to ensure updates to derived facts are performed May use derived attributes in certain circumstances use sparingly 23
Overview of Key Definitions Candidate key When there are two or more unique keys in a relation, they are called candidate keys. One is chosen as the primary key, the others are the alternate keys e.g. Employee ID and SSN Primary Key Unique, not null, unlikely to change over time Foreign Key If the primary key of the relation A also appears in relation B, then it is the foreign key in relation B The foreign key in Relation B references the relation A 24
Database Integrity Overview Domain integrity Appropriate controls must be designed to ensure that no field takes on an inappropriate value Entity integrity: primary keys Every table should have a primary key No two rows with the same primary key value No null values in a primary key Primary key values should not change over time Ensures entities are traceable 25
Referential Integrity (RI) A parent table contains a primary key which may be referenced by one or more foreign keys A dependent table (child table) contains a foreign key(s) Referential integrity the assurance that a foreign key value in one table has a matching primary key value in the related table. No restriction Delete: cascade Delete: restrict Delete: set null Enforcing RI may have an adverse impact on performance of an application. * More on this topic later 26
Referential Integrity (RI) Default Rules for Enforcing Referential Integrity Action on Parent Insert new row Insert always OK Update primary key Disallow if parent row has children Delete row Disallow if parent row has children Action on child Insert new row Disallow insert if foreign key in new row does not match a primary key value in the parent table Update foreign key Disallow update if updated foreign key does not match a primary key value in the parent table Delete existing row Delete always OK 27
Relationships Among Tables: Appliance Repair APPLIANCE Appl Appl Number Desc PK, SA NN STATUS Status Code PK FX UF WR Model No In Date Out Date Customer Problem No FK, NN Status Code FK Serial No Meaning NN, ND Fixed Unfixable Waiting Repair NN APPLIANCE EMPLOYEE Appl Employee In Time Out Time Number Number <------------------PK------------------> CUSTOMER Customer Customer Phone Number Name PK Work Performed Number NN NN NN FK FK EMPLOYEE Employee Number PK, SA BENCH Bench Number PK Employee Bench Name NN Number FK EQUIPMENT LOG (EQUIPMENT EMPLOYEE) Equip Employee Number Number <----------- PK FK FK Time Stamp In Time Stamp ------------> NN EQUIPMENT Equip Number PK Tool Type Serial No Model No Cost 28
Self-Referencing Relationships (Recursive) Foreign key that references the same table Represents relationships among members of the same set Not common but important when occurring 29
Example: Faculty Table FacSSN 098-76-5432 LEONARD 543-21-0987 VICTORIA 654-32-1098 LEONARD 765-43-2109 NICKI 876-54-3210 CRISTOPHER COLAN 987-65-4321 JULIA FacFirstName FacLastName FacRank FacSalary FacSupervisor VINCE ASST EMMANUEL PROF FIBON ASSC MACON PROF ASST MILLS ASSC $35,000 654-32-1098 $120,000 $70,000 543-21-0987 $65,000 $40,000 654-32-1098 $75,000 765-43-2109 30
Hierarchical Data Display 543-21-0987 Victoria Emmanual 654-32-1098 Leonard Fibon 098-76-5432 Leonard Vince 876-54-3210 Christopher Colan 31 31
Recursive Relationships 1:1 Recursion One person can sponsor another person. Each person is sponsored by no more than one person. Place key of sponsor in row of person being sponsored. PERSON1 Referential integrity constraint: PersonSponsored in PERSON1 must exist in Person in PERSON1 Person PersonSponsored Jones Smith Parks Myrtle Pines Smith Parks null Pines null 32
Recursive Relationship: 1:1 Alternative 1:1 Recursive relationship alternative Referential integrity constraint: PersonSponsoredBy in PERSON2 must exist in Person in PERSON2 PERSON2 Relation Person Jones Smith Parks Myrtle Pines PersonSponsoredBy null Jones Smith null Myrtle 33
1:M Recursion A customer can make many referrals to new customers. 1:N Recursive relationship. Sample data for the REFERRED_BY relationship Customer Number 100 300 400 Referred These Customers 200, 400 500 600, 700 CUSTOMER Relation CustomerData CustomerNumber ReferredBy 100 200 300 400 500 600 700 . . . . . . . null 100 null 100 300 400 400 Referential integrity constraint: ReferredBy in CUSTOMER must exist in CustomerNumber in CUSTOMER 34
M:M Recursion one doctor. This requires a new table to define the relationship. (DoctorID would be used in place of names) Doctors may treat other doctors and a doctor may be treated by more than Provider Jones Parks Smith Abernethy Franklin Receiver Smith Abernethy Jones Franklin DOCTOR relation Name Other Attributes Jones Parks Smith Abernethy O Leary Franklin .. ... .. .. .. 35
M:M Recursion (continued) Doctors may treat other doctors and a doctor may be treated by more than one doctor. This requires a new table to define the relationship. (DoctorID would be used in place of names) TREATMENT-INTERSECTION Physician Patient Jones Parks Smith Abernethy Jones Parks Franklin Jones Referential integrity constraint: Physician in TREATMENT- INTERSECTION must exist in Name in DOCTOR Smith Smith Abernethy Franklin Abernethy Abernethy Patient in TREATMENT- INTERSECTION must exist in Name in DOCTOR 36
More on M-N Relationships Rows of each table are related to multiple rows of the other table Not directly represented in the relational model Use two 1-M relationships and an associative table 37 37
More on M-N Relationships (contd) | | 38 38
Referenced Rows Referenced row Foreign keys reference rows in the associated primary key table Enrollment rows refer to Student and Offering Actions on referenced rows Delete a referenced row Change the primary key of a referenced row Referential integrity should not be violated 39 39
Possible Actions Restrict: do not permit action on the referenced row Cascade: perform action on related rows Nullify: only valid if foreign keys accept null values Default: set foreign keys to a default value 40 40
Default Rules for referential Integrity Action on Parent Insert new row Insert always OK Update primary key Disallow if parent row has children Delete row Disallow if parent row has children Action on child Insert new row Disallow insert if foreign key in new row does not match a primary key value in the parent table Update foreign key Disallow update if updated foreign key does not match a primary key value in the parent table Delete existing row Delete always OK 41
Overview Notation basics Understanding relationships Generalization hierarchies Diagram rules 43
Entity-Relationship Diagrams An entity is person, place, thing, or event for which data is collected Arelationship is a logical association between entities A relationship exists between the entities Product and Warehouse because products are stored in warehouses A logical relationship is based on the nature of the entities involved and the environment in which they exist The logical relationship between entities in an information system are graphically represented in an entity-relationship diagram (ERD) 44
Building an ER Diagram Define the entity classes Define and model the relationships between the entity classes Identify which attributes belong to which entity classes 45
Entity-Relationship Diagrams The diagram shows the basic format of an ERD for two related entities Each entity is shown as rectangle Entity rectangles are labeled with singular nouns The relationships between entities is shown as a line labeled with active verbs ENTITY 1 IS RELATED TO ENTITY 2 Basic entity-relationship diagram 46
Entity-Relationship Diagrams The diagram can be interpreted in two ways: a doctor treats a patient,and a patient is treated by a doctor Each interpretation is equally valid Entity-relationship diagrams do not depict data or information flows One entity will be above or to the left of the other entity, but this does not imply a superior/inferior relationship or a flow from the first entity to the second entity DOCTOR TREATS PATIENT The ERD is interpreted as simple English sentences 47
ERD for One-to-One Relationships (1:1) Delivery Truck Drives Driver Faculty Member Department Is Chairperson of 48
ERD for One-to-Many Relationships (1:M) The relationship between BOOK and EDITION is one-to- many: one book appears in many editions, but each edition is associated with only one book 49
ERD for Many-to-Many Relationships (M:N) The relationship between Student and Course Offering is many-to-many: one student can take many courses, and one course can have many students enrolled in it 50
