COMP9120 Week 3: Logical Database Design Semester 1, 2020 Paul Greenfield School of Computer Science Announcements › Week 2 homework due this Saturday › Assignment 1 will be released this week - You will need to be part of a group of 3 - Please ask your tutor for help during tonight’s lab if this is a problem 1 Copyright warning COMMONWEALTH OF AUSTRALIA Copyright Regulations 1969 WARNING This material has been reproduced and communicated to you by or on behalf of the University of Sydney pursuant to Part VB of the Copyright Act 1968 (the Act). The material in this communication may be subject to copyright under the Act. Any further copying or communication of this material by you may be the subject of copyright protection under the Act. Do not remove this notice. 2 Last Week › Conceptual Database Design - E-R Model - Entity type - Relationship type (key constraints and participation constraints) - IsA Hierarchy (borrowed from OO programming) - Aggregation 3 Outline of Today › Logical Database Design - Relational model (relations and schema) - Data definition language (DDL) - Integrity constraints - Mapping E-R diagrams to relations 4 Relational Data Model 5 Relational Data Model › The relational model was first proposed by Dr. E.F. ‘Ted’ Codd of IBM in 1970 in: "A Relational Model for Large Shared Data Banks”, Communications of the ACM, June 1970. - This paper caused a major revolution in the field of database management and earned Ted Codd the coveted ACM Turing Award in 1981. › Before 1970 - Various ad-hoc models: hierarchical model and network model - Writing queries was a very elaborate task › Since 1970 - Relational model dominants and is the foundation for the leading DBMS products - Simple data representation and easy to express complex queries 6 Photo of Edgar F. Codd Relational Data Model › In the relational model, a database is a collection of one or more relations, where each relation is a table with rows and columns. - This simple tabular representation enables even novice users to understand the contents of a database. - It permits the use of simple, high-level languages to query the data. › The relational model of data is based on the mathematical concept of Relation (from Discrete Mathematics) › Querying relational database has theoretical foundations: relational algebra & calculus. › Confusion alert - ‘relations’ not ‘relationships’ being discussed here - and later we’ll discuss modelling ‘relationships’ using ‘relations’! 7 Definition of a Relation 8 › Informal Definition: A relation is a named, two-dimensional table of data - Table consists of rows (records, tuples) and columns (attributes, fields) Attributes Tuples (rows, records) 5312666 5366668 5309650 Jones Smith Jin ajon1121@cs smith@mail ojin4536@it Student namesid login m m f 123 Main St 45 George 19 City Rd addressgender Conventions: we try to follow a general convention that relation names begin with a capital letter, while attribute names begin with a lower-case letter Relation Schema & Relation Instance › Formally, a relation R consists of a relation schema and a relation instance › A relation schema specifies name of relation, and name and data type (domain) of each attribute. - A1, A2, …, An are attributes, each having a domain - D1, D2, …, Dn are their corresponding domains - each attribute corresponds to one domain: dom(Ai) = Di , 1 <= i <= n - R = ( A1, A2, …, An ) is a relation schema - e.g. Student(sid: string, name: string, login: string, addr: string, gender: char) › A relation instance is a set of tuples (a table) for a schema - Each tuple has the same number of fields as attributes defined in schema - Values of a field in a tuple must conform to domain defined in schema - Relation instance often abbreviated as just relation 9 Arity and Cardinality › #fields = degree (or arity) of a relation › #rows = cardinality 10 5312666 5366668 5309650 Jones Smith Jin ajon1121@cs smith@mail ojin4536@it Student namesid login m m f 123 Main St 45 George 19 City Rd addressgender Schema (one possible) instance Degree 5 Cardinality 3 Arity and Cardinality › How many distinct tuples are in a relation instance with cardinality 22 and arity 12? 1.22 2.12 3.Up to 22 4.Up to 12 5.At least 22 6.At least 12 11 Some Remarks › ‘relation’ != ‘table’ != ‘entity’ › Requirements of a ‘relation’ - Every relation must have a unique name. - Attributes (columns) in a relation must have unique names. - The order of the columns is irrelevant. - All tuples in a relation have the same structure - Constructed from the same set of attributes (with the same domains) - Every attribute value is atomic (not multi-valued, not composite). - A relation is a set of tuples (rows), so: - Every row is unique (can’t have two rows with exactly the same values for all their fields) - The order of the rows is immaterial › The restriction to atomic attributes is also known as First Normal Form (1NF). - (Normal forms covered more in Lecture 9) 12 Example 13 › Is this a proper relation? name name gender address phones Peter Pan M Neverland 0403 567123 Dan Murphy M Alexandria 02 67831122 0431 567312 Jin Jiao F Jkdsafas sdf asdjf st Sarah Sandwoman F Glebe 02 8789 8876 Peter Pan M Neverland 0403 567123 RDBMS Table Extends Mathematical Relation › RDBMS table extends mathematical relation - RDBMS allows duplicate rows - RDBMS support an order of tuples or attributes - RDBMS allows null ‘values’ for unknown information - Codd later added NULLs to relational mathematics 14 The Special NULL ‘Value’ 15 lname fname salary birth hired Jones Peter 35000 1970 1998 Smith Susan null 1983 null Smith Alan 35000 1975 2000 › RDBMS allows a special entry NULL in a column to represent facts that are not relevant, or not yet known › Eg a new employee has not yet been allocated to a department › Eg salary and hired may not be meaningful for adjunct lecturers Pro and Con of NULL › Pro: NULL is useful because using an ordinary value with special meaning does not always work - Eg if salary=-1 is used for “unknown” in the previous example, then averages won’t be sensible › Con: NULL causes complications in the definition of many operations - Is Susan Smith included in the set of employees hired after 2000? - We shall ignore the effect of null values for now and consider their effects later › Nulls introduce ‘tri-valued’ logic (true, false, can’t-say) 16 Data Definition Language (DDL) 1 7 SQL for Interacting with RDBMS 18 › SQL (structured query language) is the standard language for interacting with RDBMS - Data definition language (DDL) - the part of SQL that supports the creation, deletion and modification of tables. - Data manipulation language (DML) - Data control language (DCL) Web Interfaces Application Front Ends SQL Interface Parser Plan Executor Optimizer Operator Evaluator File and Access Methods Buffer Manager Disk Space Manager Recovery Manager System Catalog Index Files Data Files DATABASE DBMS SQL Commands Query Evaluation Engine Transaction Manager Lock Manager Concurrency Control Creating Tables in SQL › Creation of tables / relations: CREATE TABLE name ( list-of-columns ); - Example: Create the Student table. CREATE TABLE Student (sid INTEGER, name VARCHAR(20), login VARCHAR(20), gender CHAR, address VARCHAR(50) ); - This actually specifies the schema information - Note that the type (domain) of each field is specified, and enforced by the DBMS whenever tuples are added or modified. › Many base data types available in ANSI SQL - E.g. INTEGER, REAL, CHAR, VARCHAR, DATE, … - but each system has its differences such as specific BLOB types or value range restrictions - E.g. Oracle calls a string for historical reasons VARCHAR2 - Check the documentation https://www.postgresql.org/docs/9.5/static/datatype.html 19 Base Data Types of ANSI SQL 20 Base Datatypes Description Example Values SMALLINT INTEGER BIGINT Integer values 1704, 4070 DECIMAL(p,q) NUMERIC(p,q) Fixed-point numbers with precision p and q decimal places 1003.44, 160139.9 FLOAT(p) REAL DOUBLE PRECISION floating point numbers with precision p 1.5E-4, 10E20 CHAR(q) VARCHAR(q) CLOB(q) alphanumerical character string types of fixed size q respectively of variable length of up to q chars 'The quick brown fix jumps…’, ’INFO2120’ BLOB(r) binary string of size r B’01101’, X’9E’ DATE date DATE ’1997-06-19’, DATE ’2001-08-23’ TIME time TIME ’20:30:45’, TIME ’00:15:30’ TIMESTAMP timestamp TIMESTAMP ’2002-08-23 14:15:00’ INTERVAL time interval INTERVAL ’11:15’ HOUR TO MINUTE Be very careful here – not all types in ANSI SQL are supported everywhere Example: Create Table in SQL 21 CREATE TABLE Student ( sid INTEGER, name VARCHAR(20) ); CREATE TABLE UnitOfStudy ( ucode CHAR(8), title VARCHAR(30), credit_pts INTEGER ); CREATE TABLE Enrolled ( sid INTEGER, ucode CHAR(8), semester VARCHAR ); sid Student Enrolled UnitOfStudy name semestersid ucode titleucode credit_pts Table Deletion and Modification › Deletion of tables: DROP TABLE name ; - Both the schema information and the tuples are deleted. - Example: Destroy the Student relation DROP TABLE Student ; › Existing schemas can be changed ALTER TABLE name ADD COLUMN … | ADD CONSTRAINT…| … - Huge variety of vendor-specific options; see online documentation https://www.postgresql.org/docs/9.5/static/ddl-alter.html Rename column: ALTER TABLE customers RENAME COLUMN credit_limit TO credit_amount; Add columns: ALTER TABLE countries ADD COLUMN duty_pct NUMERIC(2,2), ADD COLUMN visa_needed VARCHAR(3); 22 Inserting and Modifying tuples › Insertion of tuples into a table / relation - Syntax: INSERT INTO table [“(”list-of-columns“)”] VALUES “(“ list-of-expression “)” ; - Example: INSERT INTO Student VALUES (12345678, ‘Smith’); INSERT INTO Student (name, sid) VALUES (‘Smith’, 12345678); › Updating of tuples in a table / relation - Syntax: UPDATE table SET column“=“expression {“,”column“=“expression} [ WHERE search_condition ] ; - Example: UPDATE Student SET address = ‘4711 Water Street’ WHERE sid = 123456789; › Deleting of tuples from a table / relation - Syntax: DELETE FROM table [ WHERE search_condition ] ; - Example: DELETE FROM Student WHERE name = ‘Smith’; 23 More details on those in next week’s SQL lecture. Integrity Constraints 2 4 Integrity Constraints › Integrity Constraints (ICs): - Rules/constraints that prevent the integrity of data being compromised - These rules must always be true for any instance of the database - Value checks, foreign keys, unique, not null › ICs are part of the schema - Specified when schema is defined. - Checked when relations are modified › All instances of a relation will always meet all specified ICs. - DBMS will check that that declared ICs always hold - Stored data is more faithful to real-world meaning - Prevents some programming and data errors from corrupting database! 25 Keys › Key: a minimal set of attributes in a relation that can uniquely identify each row of that relation - Examples include employee numbers, social security numbers, etc. This is how we can guarantee that all rows are unique. - Keys can be simple (single attribute) or composite (multiple attributes) › A set of attributes is a key for a relation if : - 1. No two distinct tuples in a legal instance can have the same values in all key fields, and - 2. This is not true for any subset of the key. - Part 2 false? A superkey. › E.g., sid is a key for Student. - What about name? - And the set {sid, gender}? 26 This is a superkey. Key & Foreign Key › Possible to have multiple possible keys for a relation - Candidate keys - One of the keys is chosen (by DBA) to be the primary key (PK). - If we just say key, we typically mean candidate key › Foreign keys (FK) - Attributes that contain candidate key values for other relations - Like a `logical pointer’ - … and how we implement E-R ‘relationships’ in relational databases 27 Example for Key & Foreign Key 28 sid Student Enroll Units_of_study 31013 I2005 2005S1 I2120 DB Intro 431013 John 120 Primary key identifies each tuple of a relation, underlined by a solid line Foreign key is a (set of) attribute(s) in one relation that `refers’ to a tuple in another relation (like a `logical pointer’), underlined by a dashed line name semestersid ucode titleucode credit_pts Composite Primary Key consisting of more than one attribute. Key & Foreign Key in SQL › Primary keys and foreign keys can be specified as part of the SQL CREATE TABLE statement: - The PRIMARY KEY clause lists attributes that comprise the primary key. - The UNIQUE clause lists attributes that comprise a candidate key. - The FOREIGN KEY clause lists the attributes that comprise the foreign key and the name of the relation referenced by the foreign key. › Note that, SQL does not require every table to have a key › By default, a foreign key references the primary key attributes of the referenced table FOREIGN KEY (sid) REFERENCES Student › Reference columns in the referenced table can be explicitly specified - but must be declared as primary or candidate keys FOREIGN KEY (lecturer) REFERENCES Lecturer(empid) › Name them using CONSTRAINT clauses CONSTRAINT Student_PK PRIMARY KEY (sid) 29 Example: Primary & Foreign Keys 30 CREATE TABLE Student ( sid INTEGER, … , CONSTRAINT Student_PK PRIMARY KEY (sid) ); CREATE TABLE UoS ( ucode CHAR(8), … , CONSTRAINT UoS_PK PRIMARY KEY (ucode) ); CREATE TABLE Enrolled ( sid INTEGER, ucode CHAR(8), semester VARCHAR, CONSTRAINT Enrolled_FK1 FOREIGN KEY (sid) REFERENCES Student, CONSTRAINT Enrolled_FK2 FOREIGN KEY (ucode) REFERENCES UoS, CONSTRAINT Enrolled_PK PRIMARY KEY (sid,ucode) ); sid Student Enrolled Unit_of_Study name semestersid ucode titleucode credit_pts Key Constraint › Key Constraint: No two distinct tuples can have the same values in all key attributes › Make sure your constraints don’t disallow valid data › Example: 31 CREATE TABLE Enrolled ( sid INTEGER, cid CHAR(8), grade CHAR(2), PRIMARY KEY (sid,cid) ); “For a given student and course, there is a single grade.” CREATE TABLE Enrolled ( sid INTEGER, cid CHAR(8), grade CHAR(2), PRIMARY KEY (sid), UNIQUE (cid, grade) ); “Students can take only one course and receive a single grade for that course; further, no two students in a course receive the same grade.” vs. Foreign Key Constraint › Foreign Key Constraint (Referential Integrity): For each tuple in the referring relation whose foreign key value is , there must be a tuple in the referred relation with a candidate key that also has value  - e.g. Enrolled(sid: integer, ucode: string, semester: string) sid is a foreign key referring to Student: 32 sid ucode semester 1234 COMP5138 2012S1 3456 COMP5138 2012S1 5678 COMP5138 2012S2 5678 COMP5338 2013S1 Q: What can we say about the Student relation? ICs to Avoid Duplicate Rows › In an RDBMS, it is possible to insert a row where every attribute has the same value as an existing row - The table will then contain two identical rows - Is this what we really want? - Huge danger of inconsistencies if we miss duplicates during updates - This isn’t possible for a mathematical relation, which is a set of n-tuples 33 lname fname salary birth hired Jones Peter 35000 1970 1998 Smith Susan 75000 1983 2006 Smith Alan 35000 1975 2000 Jones Peter 35000 1970 1998 Identical rows How to Avoid Duplicate Rows? › If at least one key is specified for a table, - is it possible for the table to contain two identical rows? - No › If no key is specified for a table, - specify the entire set of attributes as a candidate key by the UNIQUE clause. 34 NOT NULL constraint › RDBMS by default allows a special entry NULL in a column to represent facts that are not relevant, or not yet known › For certain applications, it is important to specify that no value in a given column can be NULL › In SQL CREATE TABLE Student ( sid INTEGER NOT NULL, name VARCHAR(20), login VARCHAR(20) NOT NULL, gender CHAR, birthdate DATE ) ; 35 Keys and NULLs › PRIMARY KEY - Up to one per table, and must be unique - Automatically disallow NULL values › UNIQUE (candidate key) - Possibly many candidate keys (specified using UNIQUE) - According to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a UNIQUE constraint should disallow duplicate non-NULL values, but allow multiple NULL values. - Many DBMSs implement only a limited version of this, allowing a single NULL but disallowing multiple NULL values › FOREIGN KEY - By default allows NULL values - If there must be a related tuple, then must combine with NOT NULL constraint 36 Mapping E-R Diagrams to Relations 3 7 Mapping E-R Diagrams to Relations › E-R diagram consists for - Strong entity types - Weak entity types - Relationship types - Key constraints - Participation constraints - IsA Hierarchies - Aggregations › We will concentrate in the lecture on typical examples… 38 Mapping Strong Entity Types › Easy - each entity type becomes a relation - attributes → Columns - entities → Rows › Attributes - Simple attributes E-R attributes map directly onto the relation columns - Composite attributes Composite attributes are flattened out by creating a separate field for each component attribute => We use only their simple, component attributes - Multi-valued attribute Becomes a separate relation with each row having a foreign key taken from the parent entity 39 Mapping Strong Entity Types 40 › Employee entity type with composite/multi-valued attributes Employee nameempId address street city state zipcode skills empId SkillsEmployee skillstreetempId name statecity zipcode PK/FK reference between Employee table and new Skills table for multi-valued attribute. Composite attribute flattened into separate attributes Mapping Relationships without Constraints › E-R Model: the combination of the primary keys of the participating entity types forms a super key of a relationship. - Is this a candidate key? › Looking on each relationship side: this is a many-to-many relationship - 1 Employee can work in 0 to many Departments - 1 Department can have 0 to many Employees › Create a new 'relationship relation' with the primary keys of the two participating entity types as its primary key - Other relationship attributes can be added to this new relation - No other constraints needed 41 Employee DepartmentWorksIn empId name did dname Mapping Relationship Types without Constraints 42 › General relationship between Student & UnitOfStudy Student UnitOfStudyenroll cntry name sid title ucode credit_ pts semester bdate grade sid Student Enroll UnitOfStudy name semestersid ucode titleucode credit_ptsbdate cntry grade Can a student repeat a course? No. (sid,ucode) has to be unique Mapping Relationships with Key Constraints › Looking on each side of this 1:many relationship - 1 Employee works in at most 1 Department (perhaps none) - 1 Department can have 0 to many Employees › Could use the previous approach (relationship relation) with additional attribute constraints (not recommended) - The primary key of Employee is a candidate key of WorksIn - WorksIn.empId is declared as unique to force 'at most once' constraint › A better approach is merge the relationship into the originating relation - WorksIn merged into Employee (as Employee→WorksIn is 1:1 or null) - Worksin.did added as (single) FK attribute to Employee (NULL allowed) 43 Employee DepartmentWorksIn empId name did dname Mapping Relationships with Key Constraints 44 › Relationship with Key Constraint Employee DepartmentWorksIn empId name did dname since Department dnamedid Employee didempId name since 'at most 1': single-valued FK attribute, null allowed Mapping Relationships with Key Constraints 45 › Relationship with Key Constraints on both sides › Each Employee works in at most one Department › Each Department has at most one Employee - Add uniqueness constraint to foreign key (Employee.did) Employee DepartmentWorksIn empId name did dname since Department dnamedid Employee didempId name since Mapping Relationships with Key & Participation Constraints › Looking on each side of this 1:m relationship - 1 Employee works in exactly 1 Department - 1 Department can have 0 to Many Employees › Follow normal 1:m practice & merge WorksIn into Employee - Employee→Department may not be null - Add did as FK attribute to Employee (as before) - Add NOT NULL constraint to Employee.did FK attribute (bold) 46 Employee DepartmentWorksIn empId name did dname Mapping Relationships with Key & Participation Constraints 47 › Relationship with Key & Participation Constraints Employee DepartmentWorksIn empId name did dname since Department dnamedid Employee didempId name since 'exactly 1': single-valued FK attribute, null not allowed Mapping Relationships with Key & Participation Constraints 48 › Relationship with Key & Participation Constraints › NOT NULL on foreign key (Employee.did) forces ‘at least one’ › Uniqueness constraint on foreign key (Employee.did) ‘no more than one’ Employee DepartmentWorksIn empId name did dname since Department dnamedid Employee didempId name since E-R Constraints Revisited 49 Employee WorksIn Employee WorksIn Employee WorksIn Employee WorksIn WorksIn empId did 1 1 12 2 2 3 11 2 10 1 9 4 5 4 4 1 3 2 Each empId can be present >= 0 times. Missing OK, multiple times OK. Each empId can be present 0 or 1 times. Can be present exactly once, can be missing. Every empId must be present >= 1 times. Must be present at least once, multiple times OK. Every empId must be present 1 times. Must be present exactly once. Enforcing Constraints › Sometimes need more computationally expensive assertions or table constraints (and it may not always be worth doing this) 50 Employee DepartmentWorksIn empId name did dname Employee DepartmentWorksIn empId name did dname Employee DepartmentWorksIn empId name did dname Employee DepartmentWorksIn empId name did dname Exercise › Which is the correct relation mapping of the "on duty" relationship? 1. OnDuty(dutyID, startTime, endTime) 2. OnDuty(dutyID, startTime, endTime, docID, wardID, roomNo) 3. OnDuty(docID, wardID, roomNo, startTime, endTime) 4. OnDuty(docID, wardID, roomNo, startTime, endTime) 5. OnDuty(docID, wardID, roomNo, startTime, endTime) 6. OnDuty(startTime, endTime, docID, wardID, roomNo) 51 Cardinality Approach › 1:1 (one-to-one) - Merge relations (unless you want to keep them separate) - Must always be one-to-one pairing (no missing pairs) › 1:m (one-to-many) - Add primary key of ‘1’ side as foreign key attribute of ‘m’ side relation - ‘m’ can include 0 (not all ‘1’ sides keys have to be present as FKs on ‘m’ side › m:n (many-to-many) - Add ‘relationship relation’ with foreign keys from ‘m’ & ‘n’ sides as attributes - Not all primary key values have to be present as foreign keys - The most general solution, and always possible. › Cardinality is what is possible, not what exists just now - Can employees work for >1 department, even if none are doing so today? 52 Mapping Weak Entity Types › Weak Entity Types become a separate relation with a foreign key taken from the identifying owner entity - Primary key composed of: - Partial key (discriminator) of weak entity - Primary key of identifying relation (strong entity) - Mapping of attributes of weak entity as shown before 53 Employee name empIid DependentPolicy name birthdate family given Example: Mapping Weak Entity Types 54 ◼ Weak entity type ‘Dependent’ with composite partial key empId DependentEmployees given familyempId name birthdate Employee name empIid DependentPolicy name birthdate family given Foreign key Composite primary key Mapping IsA-Hierarchy › Standard way (works always, not all constraints enforced): - Distinct relations for the superclass and for each subclass - Consider each “subclass IsA superclass” separately, in a similar way to weak entity type but without partial key - Superclass attributes go into superclass relation - Subclass attributes go into each sub-relation; primary key of superclass relation becomes primary key and also foreign key of subclass relation 55 Example: Mapping IsA-Hierarchy › Table for superclass - Primary key + common attributes › Separate tables for subclasses - Primary key of superclass (foreign key of subclass) + specific attributes 56 Person Employee Customer ISA id street city Credit_ratingsalary Person Employee cityid street salaryid Customer credit_ratingid Example: Mapping Aggregations 57 › Foreign key to aggregation is key of aggregated relationship Student UnitOfStudyenroll sid ucode Enrolled By Employee empid Enroll sid ucode Employee empidsid ucode empid EnrolledBy Biological Database 58 Biological Database Schema (part) 59 -- Genes -- ----------- CREATE TABLE dbo.Genes ( GeneKey int, -- unique key for this gene (used as FK for GeneSeqs) SpeciesNo smallint, -- |(SpeciesNo, SequenceNo, GeneNo) is compound key SequenceNo smallint, -- | for this gene record. GeneNo int, -- | (GeneNos restart at Sequence, not contig) GenePID varchar(20), -- PID name for gene ('non-coding' between annotated genes) GeneName varchar(20), -- short-form gene name GeneSynonym varchar(20), -- GeneCode varchar(20), -- GeneCOG varchar(20), -- COG code for protein GeneProduct varchar(200), -- description of protein or RNA if coding sequence GeneRNA char (3), -- 'RNA' for RNA genes GeneGC int, -- GC content of this gene GeneStrand char (1), -- '+'=forward; '-'=reverse GeneStart int, -- chromosome-relative location of 'first' base GeneEnd int, -- chromosome-relative location of 'last' base GeneLength int, -- total length of gene -- Gene varchar(max), -- gene itself (moved to separate 1-1 table) CONSTRAINT PK_Genes PRIMARY KEY CLUSTERED ( SpeciesNo ASC, SequenceNo ASC, GeneNo ASC ) ON NCBISSDGroup ) ON NCBISSDGroup -- GeneSeqs -- actual gene sequence. 1-1 with Genes but separate to reduce Genes table size -------------- CREATE TABLE dbo.GeneSeqs ( GeneKey int, -- FK to parent Genes tuple Gene varchar(max), -- gene itself CONSTRAINT PK_GeneSeqs PRIMARY KEY CLUSTERED ( GeneKey ASC ) ON Primary ) ON Primary Learning Outcomes › The Relational Model - Design a relational schema for a simple use case - Identify candidate and primary keys for a relational schema - Explain the basic rules and restrictions of the relational data model - Explain the difference between candidate, primary and foreign keys - Create and modify a relational database schema using SQL - including domain types, NULL constraints and PKs/FKs - Map an ER diagram to a relational database schema › Key topics: - Relations (schemas, instances, cardinality, arity) - NULL values - Integrity constraints - Keys (candidate, primary, foreign, super, composite keys) - Domain constraints (NOT NULL, data types) - SQL DDL (CREATE/DROP TABLE) 60 References › Ramakrishnan/Gehrke (3rd edition - the ‘Cow’ book) - Chapter 3.1-3.5, plus Chapter 1.5 › Kifer/Bernstein/Lewis (2nd edition) - Chapter 3 - Chapter 4.5 for ER-diagram mappings › Molina/Ullman/Widom (2nd edition) - Chapter 2.1 - 2.3, Section 7.1 – 7.3 - Chapter 4.5 – 4.6 for ER-diagram mappings - foreign keys come later, instead relational algebra is introduced very early on; also briefly compares RDM with XML › PostgreSQL 9.5 Language Reference - https://www.postgresql.org/docs/9.5/static/index.html 61 Next Week › The Structured Query Language (SQL) › Foundations of Declarative Querying - Relational Algebra - a formal query language for the relational data model › Readings (choose one): - Ramakrishnan/Gehrke - Chapter 5.1-5.6 & Section 4.2 - Kifer/Bernstein/Lewis - Chapter 5 - Molina/Ullman/Widom - Chapter s 5.1-5.2 and 6.1 – 6.2 62
















































































































































































































































































































































































































































































































































































































































































































































































































































































































































































学霸联盟