Transcript Data model
Relational Model 4/11/2016 CPSC-4360-01, CPSC-5360-01, Lecture 4 1 Introduction The Relational Database Management System (RDBMS) has become the dominant DBMS in use today. All data are logically structured withing relations (tables). Based on the relational data model proposedby Dr E.F . Codd. Design methology will be based on the relational model 4/11/2016 CPSC-4360-01, CPSC-5360-01, Lecture 4 2 What is a data model ? Data model : Integrated collection of concepts for describing data (data requirements). A data model may comprise 3 componants : 4/11/2016 A structural part ( rules on how database to be constructed) Manipulative part (Type of operations allowed on the data) Set of integrity rules ( to ensure data accuracy) CPSC-4360-01, CPSC-5360-01, Lecture 4 3 Terminilogy In the relational model, we use relations ( tables ) to hold info about the objects that we want to represent in the database. Relation : Table with column and rows Attribute : named column of a relation Domain: set of allowable (possible)values for one or more attributes. Tuple: a record of a relation (row in the table) Relational Database - collection of normalized relations with distinct relation names. 4/11/2016 CPSC-4360-01, CPSC-5360-01, Lecture 4 4 4/11/2016 CPSC-4360-01, CPSC-5360-01, Lecture 4 5 Examples of domain for attributtes 4/11/2016 CPSC-4360-01, CPSC-5360-01, Lecture 4 6 Properties of Relations Table name is distinct from all other table names in the database. Each cell of table contains exactly one atomic (single) value. Each column has a distinct name. Values of a column are all from the same domain. Each record is distinct (no duplicate record) Order of columns has no significance. Order of records has no significance, theoretically (affect efficiency of accessing records ) © Pearson Education Limited, 2004 7 Relational Keys Each record in a table must be unique , so we need a mean to provide this uniqueness. Superkey A column, or a set of columns (minimum number), that uniquely identifies a record within a table. Candidate Key “K” that record . Irreducibility : No proper subset of K has the uniqueness property Uniqueness : value uniquely identify © Pearson Education Limited, 2004 8 “ Branch” table “city” cannot be selected as a candidate key for the Branch table . Why? What about “zipCode”? 4/11/2016 CPSC-4360-01, CPSC-5360-01, Lecture 4 9 Composite key Role table represents the name of the character played by an actor in videos. 4/11/2016 CPSC-4360-01, CPSC-5360-01, Lecture 4 10 Relational Keys Primary Key Candidate key selected to identify records uniquely within table. Alternate Keys Candidate keys that are not selected to be primary key. Example : for Branch table , if “branchNo” is the primary key then “zipCode” is the alternate key. © Pearson Education Limited, 2004 11 Relational Keys Foreign Key Column, or set of columns, within one table that matches candidate key of some (possibly same) table. Note: When a column appears in more than one table , its appearance usually represents a relationship between records of the two tables © Pearson Education Limited, 2004 12 Relational Integrity Every column has an associated domain (constraint the value that a column can take). There are 2 more important integrity rules that apply to all instances of the database: Entity integrity Referential integrity © Pearson Education Limited, 2004 13 Relational Integrity Null Represents value for a column that is currently unknown or not applicable for record. Deals with incomplete or exceptional data. Represents the absence of a value and is not the same as zero or spaces, which are values. © Pearson Education Limited, 2004 14 Relational Integrity Entity Integrity In a base table, no column of a primary key can be null. Referential Integrity If FK exists in a table, either FK value must match a candidate key value of some record in its home table or FK value must be wholly null. © Pearson Education Limited, 2004 15 Relational Integrity Business Rules Rules that define or constrain some aspect of the organization. Example : a member can only rent a maximum of 10 videos at any one time. © Pearson Education Limited, 2004 16 Relational Languages Two main languages: SQL (Structured Query Language), standardized by ISO. QBE (Query-by-Example), alternative graphical “point-and-click” way of querying database. © Pearson Education Limited, 2004 17 Schema Diagram Relational Database Keys Table 2.3 Figure 2.4 An Illustration of Integrity Rules An Example Table Students (sid: string, name: string, login: string, age: integer, gpa: real) sid 50000 53666 53688 53650 53831 53832 name Dave Jones Smith Smith Madayan Guldu login dave@cs jones@cs smith@ee smith@math madayan@music guldu@music age 19 18 18 19 11 12 gpa 3.3 3.4 3.2 3.8 1.8 2.0 Another example: Courses Courses (cid, instructor, quarter, dept) cid instructor quarter dept Carnatic101 Jane Fall 06 Music Reggae203 Bob Summer 06 Music Topology101 Mary Spring 06 Math History105 Fall 06 History Alice Keys Primary key – minimal subset of fields that is unique identifier for a tuple sid is primary key for Students cid is primary key for Courses Foreign key –connections between tables Courses (cid, instructor, quarter, dept) Students (sid, name, login, age, gpa) How do we express which students take each course?