The Relational Data Model 1 Overview Relational model
- Slides: 69
The Relational Data Model 1
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
University Database in MS Access 4 4
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 5 5
Alternative Terminology Table-oriented Set-oriented Record-oriented (RDBMS) Table (Formal Terms) Relation 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 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 Entity – a class of persons, places, objects, events, or Modeling Concepts: Entity concepts about which we need to capture and store data. • Named by a singular noun : : : 8 -8 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
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 Last Name First Name Dorm 2144 Arnold Betty Smith 3122 Taylor John Jones 3843 Simmons Lisa Smith 9844 Macy Bill 2837 Leath Heather Smith 2293 Wrench Tim Jones Primary Key 8 -12 Dorm Residence Director Smith Andrea Fernandez Jones Daniel Abidjan Foreign Key Duplicated from primary key of Dorm entity (not unique in Student entity) 12
Activity 1) 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. 2) Create the resulting tables. 13 13
Activity (cont’d) “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 (cont’d) 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 General Ledger Sub-ledger Customer number Would be represented in a table as follows: Account General Ledger Sub Ledger Customer Number 22 04 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 ORDER PART# DETAIL QTY LAST SALES FIVE SALES MONTHS SALES Order ORDER NUMBER (9 ……. OCCU RRENCES) PART# 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 1 175. 00 1 150. 00 1 140. 00 2 200. 00 2 195. 00 2 180. 00 Current (employee 1) previous starting Current (employee 2) previous starting 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 1 175. 00 1 150. 00 1 140. 00 2 200. 00 2 195. 00 2 180. 00 3 110. 00 3 105. 00 Current (employee 1) previous starting Current (employee 2) previous starting Current (employee 3) previous starting 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 Keykey Definitions • Candidate • 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 n Entity integrity: primary keys q q q 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 Action on child Insert always OK Update primary key Disallow if parent row has children Delete row Disallow if parent row has children 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 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 30
Hierarchical Data Display 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. PERSON 1 Referential integrity constraint: Person. Sponsored in PERSON 1 must exist in Person in PERSON 1 32
Recursive Relationship: 1: 1 Alternative 1: 1 Recursive relationship alternative Referential integrity constraint: Person. Sponsored. By in PERSON 2 must exist in Person in PERSON 2 Person Jones Smith Parks Myrtle Pines PERSON 2 Relation Person. Sponsored. By 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 Customer. Number 100 200 300 400 500 600 700 Referred These Customers 200, 400 500 600, 700 CUSTOMER Relation Customer. Data …. …. Referred. By null 100 300 400 Referential integrity constraint: Referred. By in CUSTOMER must exist in Customer. Number in CUSTOMER 34
Doctors may treat other doctors and a doctor may be treated by more than M: M • Recursion one doctor. This requires a new table to define the relationship. (Doctor. ID would be used in place of names) Name Jones Parks Smith Abernethy O’Leary Franklin DOCTOR relation Other Attributes …. . …… …. . 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. (Doctor. ID would be used in place of names) TREATMENT-INTERSECTION Referential integrity constraint: Physician in TREATMENTINTERSECTION must exist in Name in DOCTOR Patient in TREATMENTINTERSECTION 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 (cont’d) | | 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 Action on child Insert new row Insert always OK Update primary key Disallow if parent row has children Delete row Disallow if parent row has children 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
Entity Relationships Diagrams 42
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 • A relationship 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 ENTITY 1 • The diagram shows the basic format of an ERD for two related entities • Each entity is shown as rectangle IS RELATED TO • Entity rectangles are labeled with singular nouns • The relationships between entities is shown as a line labeled with active verbs ENTITY 2 Basic entity-relationship diagram 46
Entity-Relationship Diagrams DOCTOR • 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 TREATS • 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 PATIENT The ERD is interpreted as simple English sentences 47
ERD for One-to-One Relationships (1: 1) Driver Faculty Member Drives Is Chairperson of Delivery Truck Department 48
ERD for One-to-Many Relationships (1: M) The relationship between BOOK and EDITION is one-tomany: 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
Many-to-Many Relationships (M: N) (cont’d) An Author can write many Books and a Book can be written by more than one Author 51
Basic Symbols Many developers like to show the FK (Course. No) in the list of attributes 52 52
Cardinality Notation 53
Associative Entity Types for M-way Relationships 54
More Relationships § What is the relationship between Office and Faculty? In which table would you place the FK? § What is the relationship between Faculty and Offering? In which table would you place the FK? § Is it possible to implement this relationship as it is? More about this later. 55
Associative Entity Type Example 56
Comprehensive Example 57
Completeness Rules • Primary Key Rule: all entity types have a PK (direct, indirect, or inherited) • Naming Rule: all entity types, relationships, and attributes have a name • Cardinality Rule: cardinality is specified in both directions for each relationship • Entity Participation Rule: all entity types participate in an at least one relationship except for entity types in a generalization hierarchy 58
Activity 1) Draw a simple ERD containing the Order, Customer, and Product entities. Show attributes and PKs and FKs. 2) Choose appropriate relationship names using your common knowledge of interactions between customers, products and orders. 3) Extend the ERD to include the Employee entity. 4) Choose appropriate relationship names using your common knowledge of interactions between employees and orders. 5) The next slide describes the attributes for each entity. 59 59
Activity (cont’d) • Customer attributes: Cust. No , Cust. First. Name, Cust. Last. Name, , Cust. Street, Cust. City, Cust. State, Cust. Zip, Cust. Bal (balance. ) • Order attributes: Ord. No, Ord. Date, Ord. Name, Ord. Street, Ord. City, Ord. State, Ord. Zip. • Product attributes: Prod. No, Prod. Name, Prod. QOH, Prod. Price, Prod. Next. Ship. Date. • Employee attributes: Emp. No, Emp. First. Name, Emp. Last. Name, Emp. Phone, Emp. Email, Emp. Comm. Rate (commission rate), Emp. Dept. Name. 60
Identification Dependency Weak Entity: an entity that borrows all or part of its primary key from another entity type. The primary key of Room is a combination of Bldg. ID and Room. No 61
Identification Dependency Rules • Weak entity rule: weak entities (also called Dependent entities)have at least one identifying relationship • Identification dependency cardinality rule: the minimum and maximum cardinality must equal 1 for a weak entity in all identifying relationships 62
ERD Notation for Self-Referencing (Recursive) Relationships 63
Generalization Hierarchies 64
Inheritance • Subtypes inherit attributes of supertypes (direct and indirect) • Allows abbreviation of attribute list • Applies to code (methods) as well as attributes (data) 65
Generalization Constraints 66
Multiple Levels of Generalization 67
Comprehensive Example 68
Completeness Rules • Primary Key Rule: all entity types have a PK (direct, indirect, or inherited) • Naming Rule: all entity types, relationships, and attributes have a name • Cardinality Rule: cardinality is specified in both directions for each relationship • Entity Participation Rule: all entity types participate in an at least one relationship except for entity types in a generalization hierarchy • Generalization Hierarchy Participation Rule: at least one entity type in a generalization hierarchy participates in a relationship 69
- Data quality and data cleaning an overview
- Data quality and data cleaning an overview
- Data quality and data cleaning an overview
- The limited tuple relational calculus equals:
- Relational calculus
- Relational calculus symbols
- Object relational and extended relational databases
- Relational query language
- Dbmss use indexes for many different purposes.
- Extended relational data model
- Mapping of er model to relational model
- Master data services overview
- Mds sql server
- Chicago time
- An overview of data warehousing and olap technology
- Trajectory data mining an overview
- Methodologies for cross-domain data fusion: an overview
- Hát kết hợp bộ gõ cơ thể
- Frameset trong html5
- Bổ thể
- Tỉ lệ cơ thể trẻ em
- Voi kéo gỗ như thế nào
- Tư thế worm breton là gì
- Chúa yêu trần thế
- Kể tên các môn thể thao
- Thế nào là hệ số cao nhất
- Các châu lục và đại dương trên thế giới
- Cong thức tính động năng
- Trời xanh đây là của chúng ta thể thơ
- Mật thư tọa độ 5x5
- 101012 bằng
- Phản ứng thế ankan
- Các châu lục và đại dương trên thế giới
- Thơ thất ngôn tứ tuyệt đường luật
- Quá trình desamine hóa có thể tạo ra
- Một số thể thơ truyền thống
- Cái miệng bé xinh thế chỉ nói điều hay thôi
- Vẽ hình chiếu vuông góc của vật thể sau
- Nguyên nhân của sự mỏi cơ sinh 8
- đặc điểm cơ thể của người tối cổ
- Thế nào là giọng cùng tên? *
- Vẽ hình chiếu đứng bằng cạnh của vật thể
- Fecboak
- Thẻ vin
- đại từ thay thế
- điện thế nghỉ
- Tư thế ngồi viết
- Diễn thế sinh thái là
- Các loại đột biến cấu trúc nhiễm sắc thể
- Số nguyên tố là
- Tư thế ngồi viết
- Lời thề hippocrates
- Thiếu nhi thế giới liên hoan
- ưu thế lai là gì
- Khi nào hổ mẹ dạy hổ con săn mồi
- Sự nuôi và dạy con của hươu
- Sơ đồ cơ thể người
- Từ ngữ thể hiện lòng nhân hậu
- Thế nào là mạng điện lắp đặt kiểu nổi
- Spark sql: relational data processing in spark
- Modeling relational data with graph convolutional networks
- Dimensional modeling vs relational modeling
- Relational data structures
- What is this
- Relational database constraints
- The six stage model of relationships
- Ducks filtering model
- Circumscribing stage of a relationship
- Advantages of relational database model
- Sponsorship business model