Database Design The Relational Model Text Ch5

1. Database Design The Relational Model Text Ch5 Database Design lecture 21

2. Relational Model • In the relational model, data are organized into tables. • A table corresponds to the mathematical concept of a relation (come back shortly). • The table must have a name (a.k.a the relation name). • Each column must have a name, known as an attribute. • Each row (not including the table heading row) is called a tuple. If the tuple has n elements then it is called ann-tuple. • The number of columns (resp. rows) is called thedegree or arity (resp. cardinality) of the relation. Database Design lecture 22

3. Table (Relation) Example 1 A Student table in a university database Database Design lecture 23

4. Relation Schema and Instance • Let R be the name of a relation, and A1,…, An be theattributes. Then R(A1,…, An ) is called theschemaof the relation. • Each attribute of a relation must have a fixed set from which to obtain values. Eg, • Name : the set of all possible names • Age: {0, 1, 2,…, 150} The set corresponding to attribute Ai is called the domain of Ai , denoted dom(Ai ). • At different times, the relation may contain different tuples. Each possible set of tuples is called a state (or an instance) of the relation. Database Design lecture 24

5. Relational Database Schema and Instance • A relational database – a collection of relations. • The schemas of the relations collectively (together with a set of integrity constraints – see slide 13) is called the relationaldatabase schema. • The relations in a database should have distinct names. • Attributes within the same relation should have distinct names. • A database (instance) consists of the instances (one for each relation) of the relations. Database Design lecture 25

6. Data Inside a Relation • Atomic value in each cell • The data item in each table cell must be atomic • can not be divided into smaller components • Atomicity is a relative concept, it depends on the application. • A special value NULL can appear in any cell • represents unknown value or non-existent value • eg, in Student table, a student may have no phone number, or his phone number may be unknown. In such cases, the corresponding value is NULL • If t is a tuple, and A is an attribute, then the value of attribute A in t is denoted t[A]. Similarly t[A1, A3] denotes the subtuple of values of the attributes A1 and A3, and so forth. Database Design lecture 26

7. Mathematical Definition of Relations • Formally, given sets D1, D2, …. Dn a relation r is a subset of D1 x D2 x … x DnThus a relation is a set of n-tuples (a1, a2, …, an) where ai Di • Example: if student_number = set of 6 digit numbers student_name = {Jones, Smith, Curry, Lindsay}student_city = {Sydney, Brisbane, Melbourne, GoldCoast} Then r = { (100001, Jones, Brisbane), (100002, Curry, GoldCoast), (100003, Lindsay, Brisbane), (100004, Smith, Sydney)} is a relation over student_number x student_name x student_city Database Design lecture 27

8. Properties of Relations • No duplicate tuples within a relation • By definition sets do not contain duplicate elements, hence no tuples should appear more than once. • Ordering of tuples has no significance • elements in a set has no ordering • also in the sense that no information is lost or added when different orders are used. • but it may affect performance! • Ordering of attributes • has no significance in the sense that no information is lost or added if the attributes are ordered differently. • BUT it is important when a tuple is considered an ordered list - for easy presentation. • Alternative definition of tuple can make the ordering unimportant – a tuple is a mapping from attributes to values. Database Design lecture 28

9. Relational Keys • Superkey • An attribute, or a set of attributes, that can uniquely identify a tuple within a relation. • Every relation has at least one superkey -- all attributes of the relation • Key (Candidate Key) • Superkey (K) such that no proper subset is a superkey within the relation. • In each tuple of R, values of K uniquely identify that tuple. • Removing any attribute from K, K will no longer be a superkey. Database Design lecture 29

10. Relational Keys • Primary Key (PK) • Candidate key selected to identify tuples uniquely within relation. PK attributes will be underlined. • Alternate Keys • Candidate keys that are not selected to be primary key. • Foreign Key (FK) • Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation. Database Design lecture 210

11. Foreign Keys Example Subjects(subjNo, subjName, points) Students(studNo, Name, Addr, Phone) Enrollment(studNo, subjNo, semester, grade) In enrollment, studNo is a FK which references Students subjNo is a FK which references Subjects Database Design lecture 211

12. Another Example of FKs Database Design lecture 212

13. Relational Constraints • Domain constraints • Specify the possible values an attribute can take • Null-value constraints • Specify whether Null value is allowed in an attribute • Key constraints • PK, AK • Entity integrity • No PK value can be NULL • Referential integrity • FK values must either be NULL or appear in corresponding attribute(s) of referenced relation. • Data dependencies, Enterprise rules (later) Database Design lecture 213

14. Example Database Design lecture 214

15. Diagram Representation of FKs Database Design lecture 215

16. Alternative Schema Diagram Department Dept_locations DNAME DNUMBER MGRSSN MGRSTARTDATE Project DNUMBER DLOCATION PNAME PNUMBER PLOCATION DNUM Employee Dependent FNAME MINT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DNO ESSN DEPDENT_NAME SEX BDATE RELATIONSHIP Works_on ESSN PNO HOURS Database Design lecture 216