Relational Model and ER Diagram part 1.pdf
Document Details
Uploaded by BuoyantLithium
An-Najah National University
Full Transcript
# Relational Model ## ER-Diagram ## The Relational Model The relational model was introduced in 1970 by E. F. Codd (of IBM) in his landmark paper “A Relational Model of Data for Large Shared Databanks" (Communications of the ACM, June 1970, pp. 377-387). The relational model represented a major bre...
# Relational Model ## ER-Diagram ## The Relational Model The relational model was introduced in 1970 by E. F. Codd (of IBM) in his landmark paper “A Relational Model of Data for Large Shared Databanks" (Communications of the ACM, June 1970, pp. 377-387). The relational model represented a major breakthrough for both users and designers. To use an analogy, the relational model produced an "automatic transmission" database to replace the "standard transmission" databases that preceded it. Its conceptual simplicity set the stage for a genuine database revolution. The relational model foundation is a mathematical concept known as a relation. To avoid the complexity of abstract mathematical theory, you can think of a relation (sometimes called a table) as a matrix composed of intersecting rows and columns. Each row in a relation is called a tuple. Each column represents an attribute. The relational model also describes a precise set of data manipulation constructs based on advanced mathematical concepts. ## Characteristics of a Relational Table | Number | Description | |---|---| | 1 | A table is perceived as a two-dimensional structure composed of rows and columns. | | 2 | Each table row (*tuple*) represents a single entity occurrence within the entity set. | | 3 | Each table column represents an attribute, and each column has a distinct name. | | 4 | Each intersection of a row and column represents a single data value. | | 5 | All values in a column must conform to the same data format. | | 6 | Each column has a specific range of values known as the attribute *domain*. | | 7 | The order of the rows and columns is immaterial to the DBMS. | | 8 | Each table must have an attribute or combination of attributes that uniquely identifies each row. | ## Student Table Attribute Values **Table name:** STUDENT **Database name:** Ch03_TinyCollege | STU NUM | STU_LNAME | STU_FNAME | STU_INIT | STU DOB | STU_HRS | STU_CLASS | STU_GPA | STU TRANSFER | DEPT CODE | STU_PHONE | PROF_NUM | |---|---|---|---|---|---|---|---|---|---|---|---| | 321452 | Bowser | William | C | 12-Feb-1985 | 42 So | 2.84 | No BIOL | 2134 | 205 | | 324257 | Smithson | Anne | K | 15-Nov-1991 | 81 Jr | 3.27 | Yes CIS | 2256 | 222 | | 324258 | Brewer | Juliette | | 23-Aug-1979 | 36 So | 2.26 | Yes ACCT | 2256 | 228 | | 324269 | Oblonski | Walter | H | 16-Sep-1986 | 66 Jr | 3.09 | No CIS | 2114 | 222 | | 324273 | Smith | John | D | 30-Dec-1968 | 102 Sr | 2.11 | Yes ENGL | 2231 | 199 | | 324274 | Katinga | Raphael | P | 21-Oct-1989 | 114 Sr | 3.15 | No ACCT | 2267 | 228 | | 324291 | Robertson | Gerald | T | 08-Apr-1983 | 120 Sr | 3.87 | No EDU | 2267 | 311 | | 324299 | Smith | John | B | 30-Nov-1996 | 15 Fr | 2.92 | No ACCT | 2315 | 230 | | Attribute | Description | |---|---| | STU_NUM | = Student number | | STU_LNAME | = Student last name | | STU_FNAME | = Student first name | | STU_INIT | = Student middle initial | | STU_DOB | = Student date of birth | | STU_HRS | = Credit hours earned | | STU_CLASS | = Student classification | | STU_GPA | = Grade point average | | STU_TRANSFER | = Student transferred from another institution | | DEPT_CODE | = Department code | | STU_PHONE | = 4-digit campus phone extension | | PROF_NUM | = Number of the professor who is the student's advisor | ## ER-Diagram The image shows a simple ER diagram with the relations "Staff" and "Branch". The relation "Branch" has attributes branchNo, street, city and postcode. The relation "Staff" has attributes staffNo, fName, IName, position, sex, DOB, salary and branchNo. The foreign key "branchNo" connects the two relations. The primary key for the relation "Branch" is "branchNo". The primary key for the relation "Staff" is "staffNo". The diagram also shows the degree and cardinality of the relationships. ## Relational Keys As stated earlier, there are no duplicate tuples within a relation. Therefore, we need to be able to identify one or more attributes (called *relational keys*) that uniquely identifies each tuple in a relation. ### Superkey An attribute, or set of attributes, that uniquely identifies a tuple within a relation. A superkey uniquely identifies each tuple within a relation. However, a superkey may contain additional attributes that are not necessary for unique identification, and we are interested in identifying superkeys that contain only the minimum number of attributes necessary for unique identification. ### Candidate Key A superkey such that no proper subset is a superkey within the relation. There may be several candidate keys for a relation. When a key consists of more than one attribute, we call it a *composite key*. Consider the Branch relation shown in Figure 4.1. Given a value of city, we can determine several branch offices (for example, London has two branch offices). This attribute cannot be a candidate key. On the other hand, because DreamHome allocates each branch office a unique branch number, given a branch number value, branchNo, we can determine at most one tuple, so that branchNo is a candidate key. Similarly, postcode is also a candidate key for this relation. ### Primary Key The candidate key that is selected to identify tuples uniquely within the relation. ### Foreign Key An attribute, or set of attributes, within one relation that matches the candidate key of some (possibly the same) relation. ## Representing Relational Database Schemas A relational database consists of any number of normalized relations. The relational schema for part of the DreamHome case study is: **Relation** **Attributes** Branch (branchNo, street, city, postcode) Staff (staffNo, fName, IName, position, sex, DOB, salary, branchNo) PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) Client (clientNo, fName, IName, telNo, prefType, maxRent, eMail) PrivateOwner (ownerNo, fName, IName, address, telNo, eMail, password) Viewing (clientNo, propertyNo, viewDate, comment) Registration (clientNo, branchNo, staffNo, dateJoined) ## DreamHome Database **Branch** | branchNo | street | city | postcode | |---|---|---|---| | B005 | 22 Deer Rd | London | SW1 4EH | | B007 | 16 Argyll St | Aberdeen | AB2 3SU | | B003 | 163 Main St | Glasgow | G11 9QX | | B004 | 32 Manse Rd | Bristol | BS99 1NZ | | B002 | 56 Clover Dr | London | NW10 6EU | **Staff** | staffNo | fName | IName | position | sex | DOB | salary | branchNo | |---|---|---|---|---|---|---|---| | SL21 | John | White | Manager | M | 1-Oct-45 | 30000 | B005 | | SG37 | Ann | Beech | Assistant | F | 10-Nov-60 | 12000 | B003 | | SG14 | David | Ford | Supervisor | M | 24-Mar-58 | 18000 | B003 | | SA9 | Mary | Howe | Assistant | F | 19-Feb-70 | 9000 | B007 | | SG5 | Susan | Brand | Manager | F | 3-Jun-40 | 24000 | B003 | | SL41 | Julie | Lee | Assistant | F | 13-Jun-65 | 9000 | B005 | **PropertyForRent** | propertyNo | street | city | postcode | type | rooms | rent | ownerNo | staffNo | branchNo | |---|---|---|---|---|---|---|---|---|---| | PA14 | 16 Holhead | Aberdeen | AB7 5SU | House | 6 | 650 | CO46 | SA9 | B007 | | PL94 | 6 Argyll St | London | NW2 | Flat | 4 | 400 | CO87 | SL41 | B005 | | PG4 | 6 Lawrence St | Glasgow | G11 9QX | Flat | 3 | 350 | CO40 | | B003 | | PG36 | 2 Manor Rd | Glasgow | G32 4QX | Flat | 3 | 375 | CO93 | SG37 | B003 | | PG21 | 18 Dale Rd | Glasgow | G12 | House | 5 | 600 | CO87 | SG37 | B003 | | PG16 | 5 Novar Dr | Glasgow | G12 9AX | Flat | 4 | 450 | CO93 | SG14 | B003 | **Client** | clientNo | fName | IName | telNo | prefType | maxRent | eMail | |---|---|---|---|---|---|---| | CR76 | John | Kay | 0207-774-5632 | Flat | 425 | [email protected] | | CR56 | Aline | Stewart | 0141-848-1825 | Flat | 350 | [email protected] | | CR74 | Mike | Ritchie | 01475-392178 | House | 750 | [email protected] | | CR62 | Mary | Tregear | 01224-196720 | Flat | 600 | [email protected] | **PrivateOwner** | ownerNo | fName | IName | address | telNo | eMail | Password | |---|---|---|---|---|---|---| | CO46 | Joe | Keogh | 2 Fergus Dr, Aberdeen AB2 7SX | 01224-861212 | [email protected] | ******** | | CO87 | Tina | Farrell | 6 Achray St, Glasgow G32 9DX | 0141-357-7419 | [email protected] | ******** | | CO40 | Carol | Murphy | 63 Well St, Glasgow G42 | 0141-943-1728 | [email protected] | ******** | | CO93 | Tony | Shaw | 12 Park Pl, Glasgow G4 0QR | 0141-225-7025 | [email protected] | ******** | **Viewing** | clientNo | propertyNo | viewDate | comment | |---|---|---|---| | CR56 | PA14 | 24-May-13 | too small | | CR76 | PG4 | 20-Apr-13 | too remote | | CR56 | PG4 | 26-May-13 | | | CR62 | PA14 | 14-May-13 | no dining room | | CR56 | PG36 | 28-Apr-13 | | **Registration** | clientNo | branchNo | staffNo | dateJoined | |---|---|---|---| | CR76 | B005 | SL41 | 2-Jan-13 | | CR56 | B003 | SG37 | 11-Apr-12 | | CR74 | B003 | SG37 | 16-Nov-11 | | CR62 | B007 | SA9 | 7-Mar-12 | ## Glossary **Relation:** A relation is a table with columns and rows. **Attribute:** An attribute is a named column of a relation. **Domain:** A domain is the set of allowable values for one or more attributes. **Tuple:** A tuple is a row of a relation. **Degree:** The degree of a relation is the number of attributes it contains. **Cardinality:** The cardinality of a relation is the number of tuples it contains. **Relational database:** A collection of normalized relations with distinct relation names. ## Domain Definitions | Attribute | Domain Name | Meaning | Domain Definition | |---|---|---|---| | branchNo | BranchNumbers | The set of all possible branch numbers | Character: size 4, range B001-B999 | | street | StreetNames | The set of all street names in Britain | Character: size 25 | | city | CityNames | The set of all city names in Britain | Character: size 15 | | postcode | Postcodes | The set of all postcodes in Britain | Character: size 8 | | sex | Sex | The sex of a person | Character: size 1, value M or F | | DOB | DatesOfBirth | Possible values of staff birth dates | Date, range from 1-Jan-20, format dd-mmm-yy | | salary | Salaries | Possible values of staff salaries | Monetary: 7 digits, range 6000.00-40000,00 |
