Lecture #2 ER-Model and The Relational Model

1. Lecture #2 ER-Model and The Relational Model

2. Agenda • Previous Lecture • The Entity-Relationship Model • The Relational Model

3. In the Previous lecture • We found that: • What is the Database Management System (DBMS) • File Systems versus a DBMS • Advantages of a DBMS • Structure of a DBMS

4. Advantages of DBMS • Data Independence • Data Integrity and Security • Data Administration • Concurrent Access and Crash Recovery • Reduced Application Development Time

5. Book Contents • Database Design and Application Development: How can a user describe a real-world enterprise (e.g., a university) in terms of the data stored in a DBMS? What factors must be considered in deciding how to organize the stored data? How can we develop applications that rely upon a DBMS? (Chapters 2, 3, 6, 7, 19, 20, and 21.) • Data Analysis: How can a user answer questions about the enterprise by posing queries over the data in the DBMS? (Chapters 4 and 5) • Concurrency and Robustness: How does a DBMS allow many users to access data concurrently, and how does it protect the data in the event of system failures? (Chapters 16, 17, and 18.) • Efficiency and Scalability: How does a DBMS store large datasets and answer questions against this data efficiently? (Chapters 8, 9, la, 11, 12, 13, 14, and 15.)

7. The Entity-Relationship Model

8. Database Design Process • Requirements Analysis • Conceptual Database Design • Logical Database Design • Schema Refinement • Physical Database Design • Application and Security Design

9. Database Design Process • Requirement collection and analysis • DB requirements and functional requirements • Conceptual DB design using a high-level model • Easier to understand and communicate with others • Logical DB design (data model mapping) • Conceptual schema is transformed from a high-level data model into implementation data model • Physical DB design • Internal data structures and file organizations for DB are specified

10. Overview of Database Design • Conceptual design: (ER Model is used at this stage.) • What are the entities and relationships in the enterprise? • What information about these entities and relationships should we store in the database? • What are the integrity constraints or business rules that hold? • A database `schema’ in the ER Model can be represented pictorially (ER diagrams). • An ER diagram can be mapped into a relational schema.

11. name ssn lot Employees ER Model Basics • Entity: Real-world object distinguishable from other objects. An entity is described (in DB) using a set of attributes. • Entity Set: A collection of similar entities. E.g., all employees. • All entities in an entity set have the same set of attributes. • Each entity set has a key. • Each attribute has a domain.

12. name ssn lot Employees ER Model Basics • Key and key attributes: • Key: a unique value for an entity • Key attributes: a group of one or more attributes that uniquely identify an entity in the entity set • Super key, candidate key, and primary key • Super key: a set of attributes that allows to identify and entity uniquely in the entity set • Candidate key: minimal super key • There can be many candidate keys • Primary key: a candidate key chosen by the designer • Denoted by underlining in ER attributes

13. ER Model Basics (Contd.) name ssn lot • Relationship: Association among two or more entities. e.g., Jack works in Pharmacy department. • Relationship Set: Collection of similar relationships. Employees since name dname super-visor subor-dinate ssn budget lot did Reports_To Works_In Employees Departments

14. since name dname ssn lot Employees Manages Key Constraints did budget • Consider Works_In: An employee can work in many departments; a dept can have many employees. • In contrast, each dept has at most one manager, according to the key constrainton Manages. Departments 1-to-1 1-to Many Many-to-1 Many-to-Many

15. Department Example ER major offers faculty • An ER diagram represents several assertions about the real world. What are they? • A DB is judged correct if it captures ER diagram correctly. Courses teaches Professor advisor enrollment Students

16. Participation Constraints • Does every department have a manager? • If so, this is a participation constraint: the participation of Departments in Manages is said to be total (vs. partial). • Every Departments entity must appear in an instance of the Manages relationship. since since name name dname dname ssn did did budget budget lot Departments Employees Manages Works_In since

17. Weak Entities • A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. • Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner, many weak entities). • Weak entity set must have total participation in this identifying relationship set. name cost pname age ssn lot Policy Dependents Employees

18. The Relational Model

19. Relational Database: Definitions • Relational database:a set of relations • Relation: made up of 2 parts: • Schema:specifiesname of relation, plus name and type (domain) of each column (attribute). • E.G. Students(sid: string, name: string, gpa: real). • Instance : a table, with rows and columns. #Rows = cardinality, #fields = degree / arity. • Can think of a relation as a setof rows or tuples(i.e., all rows are distinct).

20. Example Instance of Students Relation

21. Notes on Number of Relations • Number of relations: • Is it better to reduce the # of relations? • Combining 2 relations • Deposit (b-name, acct#, c-name, balance) • Customer (c-name, street, city, zip)  Account (b-name, acct#, c-name, bal, street, city, zip) • Potential problems: wasteful repetition, complicated update, null-value problem, information lostreducing the # of relations is not necessarily good

22. Relational Query Languages • A major strength of the relational model: supports simple, powerful querying of data. • Queries can be written intuitively, and the DBMS is responsible for efficient evaluation. • The key: precise semantics for relational queries. • Allows the optimizer to extensively re-order operations, and still ensure that the answer does not change.

23. The SQL Query Language • Developed by IBM (system R) in the 1970s • Need for a standard since it is used by many vendors • Standards: • SQL-86 • SQL-89 (minor revision) • SQL-92 (major revision) • SQL-99 (major extensions, current standard)

24. The SQL Query Language • To find all 18 year old students, we can write: SELECT * FROMStudents S WHERES.age=18 • To find just names and logins, replace the first line: SELECT S.name, S.login

25. Querying Multiple Relations SELECT S.name, E.cid FROM Students S, Enrolled E WHERE S.sid=E.sid ANDE.grade=“A” • What does the following query compute? Given the following instances of Enrolled and Students: we get:

26. Creating Relations in SQL • Creates the Students relation. Observe that the type (domain) of each field is specified, and enforced by the DBMS whenever tuples are added or modified: domain constraints • As another example, the Enrolled table holds information about courses that students take. CREATE TABLE Students (sid: CHAR(20), name: CHAR(20), login: CHAR(10), age: INTEGER, gpa: REAL) CREATE TABLE Enrolled (sid: CHAR(20), cid: CHAR(20), grade: CHAR(2))

27. Destroying and Altering Relations • Destroys the relation Students. The schema information and the tuples are deleted. DROP TABLE Students ALTER TABLE Students ADD COLUMN firstYear: integer • The schema of Students is altered by adding a new field; every tuple in the current instance is extended with a null value in the new field.

28. Adding and Deleting Tuples Can insert a single tuple using: INSERT INTO Students (sid, name, login, age, gpa) VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3.2) Can delete all tuples satisfying some condition (e.g., name = Smith): DELETEFROMStudents S WHERES.name = ‘Smith’

29. Integrity Constraints (ICs) • IC: condition that must be true for any instance of the database; e.g., domain constraints. • ICs are specified when schema is defined. • ICs are checked when relations are modified. • A legalinstance of a relation is one that satisfies all specified ICs. • DBMS should not allow illegal instances. • If the DBMS checks ICs, stored data is more faithful to real-world meaning. • Avoids data entry errors, too!

30. Primary Key Constraints • A set of fields is a keyfor a relation if : 1. No two distinct tuples can have same values in all key fields, and 2. This is not true for any subset of the key. • Part 2 false? A superkey. • If there’s >1 key for a relation, one of the keys is chosen (by DBA) to be the primary key. • E.g., sid is a key for Students. • What about name? • Is the set {sid, name} a superkey? • What about {login, age}? Primary key vs candidate keys

31. Primary and Candidate Keys in SQL • Possibly many candidate keys(specified using UNIQUE), one of which is chosen as the primary key. CREATE TABLE Enrolled (sidCHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid), UNIQUE (cid, grade) ) CREATE TABLE Enrolled (sidCHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid) )

32. Primary and Candidate Keys in SQL • “For a given student and course, there is a single grade.” vs. “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.” • Used carelessly, an IC can prevent the storage of database instances that arise in practice! CREATE TABLE Enrolled (sidCHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid) ) CREATE TABLE Enrolled (sidCHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid), UNIQUE (cid, grade) )

33. Foreign Keys, Referential Integrity • Foreign key : Set of fields in one relation that is used to `refer’ to a tuple in another relation. (Must correspond to primary key of the second relation.) Like a `logical pointer’. • E.g. sid is a foreign key referring to Students: • Enrolled(sid: string, cid: string, grade: string) • If all foreign key constraints are enforced, referential integrity is achieved, i.e., no dangling references. • Can you name a data model w/o referential integrity? • Links in HTML!

34. Foreign Keys in SQL • Only students listed in the Students relation should be allowed to enroll for courses. CREATE TABLE Enrolled (sidCHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid), FOREIGN KEY (sid) REFERENCESStudents ) Enrolled Students

35. Enforcing Referential Integrity • Consider Students and Enrolled; sid in Enrolled is a foreign key that references Students. • What should be done if an Enrolled tuple with a non-existent student id is inserted? • What should be done if a Students tuple is deleted? • Also delete all Enrolled tuples that refer to it. • Disallow deletion of a Students tuple that is referred to. • Set sid in Enrolled tuples that refer to it to a default sid. • (In SQL, also: Set sid in Enrolled tuples that refer to it to a special value null, denoting `unknown’ or `inapplicable’.) • Similar if primary key of Students tuple is updated.

36. Referential Integrity in SQL CREATE TABLE Enrolled (sidCHAR(20), cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid), FOREIGN KEY (sid) REFERENCESStudents ON DELETE CASCADE ON UPDATE NO ACTION ) • SQL/92 and SQL:1999 support all 4 options on deletes and updates. • Default is NO ACTION (delete/update is rejected) • CASCADE (also delete all tuples that refer to deleted tuple) • SET NULL / SET DEFAULT(sets foreign key value of referencing tuple)

37. Where do ICs Come From? • ICs are based upon the semantics of the real-world enterprise that is being described in the database relations. • We can check a database instance to see if an IC is violated, but we can NEVER infer that an IC is true by looking at an instance. • An IC is a statement about all possible instances! • From example, we know name is not a key, but the assertion that sid is a key is given to us. • Key and foreign key ICs are the most common; more general ICs supported too.

38. name ssn lot Employees Logical DB Design: ER to Relational CREATE TABLE Employees (ssnCHAR(11), name CHAR(20), lot INTEGER, PRIMARY KEY (ssn)) • Entity sets to tables:

39. Relationships in ER Model • Relationship: Association among two or more entities. e.g., Jack works in Pharmacy department. • Relationship Set: Collection of similar relationships. • An n-ary relationship set R relates n entity sets E1 ... En; each relationship in R involves entities e1 in E1, ..., en in En • Same entity set could participate in different relationship sets, or in different “roles” in same set. since name dname ssn budget lot did Works_In Employees Departments

40. Relationship Sets to Tables CREATE TABLE Works_In( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments) • In translating a relationship set to a relation, attributes of the relation must include: • Keys for each participating entity set (as foreign keys). • This set of attributes forms a superkey for the relation. • All descriptive attributes.

41. Relationships in ER Model • Define Works_In2 table that captures all the available information in the ER diagram. since name dname ssn budget lot did Works_In2 Employees Departments addr capacity Locations

42. Relationship without Constraints • We can capture a ternary relationship as follows. CREATE TABLE Works_In2( ssn CHAR(11), did INTEGER, addr CHAR(20), since DATE, PRIMARY KEY (ssn, did, addr), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (addr) REFERENCES Locations, FOREIGN KEY (did) REFERENCES Departments,)

43. Assignment • Read the first two tutorials found in the tutorials area and try to implement them in your system

44. Thanks