Wednesday, May 6, 2020
Entity-Relationship Diagrams and the Relational Model free essay sample
Abstraction, Data Independence ââ¬â Query Languages ââ¬â Efficiency (for most tasks) ââ¬â Security ââ¬â Data Integrity Data Models DBMS models real world â⬠¢ Data Model is link between userââ¬â¢s view of the world and bits stored in computer â⬠¢ Many models exist â⬠¢ We think in terms of.. ââ¬â Relational Model (clean and common) ââ¬â Entity-Relationship model (design) ââ¬â XML Model (exchange) Student (sid: string, name: string, login: string, age: integer, gpa:real) 10101 11101 Why Study the Relational Model? â⬠¢ Most widely used model. â⬠¢ ââ¬Å"Legacy systemsâ⬠in older models ââ¬â e. g. , IBMââ¬â¢s IMS Steps in Database Design â⬠¢ Requirements Analysis ââ¬â user needs; what must database do? Conceptual Design ââ¬â high level description (often done w/ER model) ââ¬â Rails encourages you to work here â⬠¢ Logical Design ââ¬â translate ER into DBMS data model ââ¬â Rails requires you to work h ere too â⬠¢ Schema Refinement ââ¬â consistency, normalization â⬠¢ Physical Design indexes, disk layout â⬠¢ Security Design who accesses what, and how â⬠¢ Object-oriented concepts merged in ââ¬â ââ¬Å"Object-Relationalâ⬠ââ¬â two variants â⬠¢ Object model known to the DBMS â⬠¢ Object-Relational Mapping (ORM) outside the DBMS ââ¬â A la Rails â⬠¢ XML features in most relational systems ââ¬â Can export XML interfaces ââ¬â Can provide XML storage/retrieval Conceptual Design â⬠¢ What are the entities and relationships in the enterprise? â⬠¢ What information about these entities and relationships should we store in the database? â⬠¢ What integrity constraints or business rules hold? â⬠¢ A database `schemaââ¬â¢ in the ER Model can be represented pictorially (ER diagrams). â⬠¢ Can map an ER diagram into a relational schema. ER Model Basics ssn name lot Employees â⬠¢ Entity: Real-world object, distinguishable from other objects. An entity is described 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. (Until we consider hierarchies, anyway! ) ââ¬â Each entity set has a key (underlined). ââ¬â Each attribute has a domain. ER Model Basics (Contd. ) name ssn Employees lot Works_In since did dname budget Departments ER Model Basics (Cont. ) name ssn lot dname did budget Departments since Employees supervisor Works_In subordinate â⬠¢ Relationship: Association among two or more entities. E. g. , Attishoo works in Pharmacy department. ââ¬â relationships can have their own attributes. Reports_To â⬠¢ 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 ? E1, , en ? En â⬠¢ Same entity set can participate in different relationship sets, or in different ââ¬Å"rolesâ⬠in the same set. name ssn lot since did dname budget â⬠¦to be clearâ⬠¦ â⬠¢ Recall that each relationship has exactly one element of each Entity Set ââ¬â ââ¬Å"1-Mâ⬠is a constraint on the Relationship Set, not each relationship Key Constraints An employee can work in many departments; a dept can have m any employees. Employees Manages Departments Works_In since â⬠¢ Think of 1-M-M ternary relationship In contrast, each dept has at most one manager, according to the key constraint Many-to- 1-to Many on Manages. Many 1-to-1 Participation Constraints â⬠¢ Does every employee work in a department? â⬠¢ If so, this is a participation constraint ââ¬â the participation of Employees in Works_In is said to be total (vs. partial) ââ¬â What if every department has an employee working in it? 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 ssn lot cost pname age â⬠¢ Basically means ââ¬Å"at least oneâ⬠name ssn Employees lot Manages since did dname budget Departments Works_In Means: ââ¬Å"exactly oneâ⬠since Employees Policy Dependents Weak entities have only a ââ¬Å"partial keyà ¢â¬ (dashed underline) Binary vs. Ternary Relationships ssn name lot Covers pname age Binary vs. Ternary Relationships (Contd. ) â⬠¢ Previous example illustrated a case when two binary relationships were better than one ternary relationship. If each policy is owned by just 1 employee: Employees Dependents Bad design Key constraint on Policies would mean policy can name ssn only cover 1 dependent! â⬠¢ Think through all the constraints in the 2nd diagram! policyid lot Policies cost pname age Employees Purchaser Dependents Beneficiary â⬠¢ An example in the other direction: a ternary relation Contracts relates entity sets Parts, Departments and Suppliers, and has descriptive attribute qty. No combination of binary relationships is an adequate substitute. (With no new entity sets! ) Better design policyid Policies cost Binary vs. Ternary Relationships (Contd. ) qty Parts Contract Departments Summary so far â⬠¢ Entities and Entity Set (boxes) â⬠¢ Relationships and Relationship sets (diamonds) Suppliers Parts VS. needs Departments ââ¬â binary ââ¬â n-ary can-supply Suppliers deals-with â⬠¢ Key constraints (1-1,1-N, M-N, arrows) â⬠¢ Participation constraints (bold for Total) â⬠¢ Weak entities require strong entity for key ââ¬â S ââ¬Å"can-supplyâ⬠P, D ââ¬Å"needsâ⬠P, and D ââ¬Å"deals-withâ⬠S does not imply that D has agreed to buy P from S. ââ¬â How do we record qty? Administrivia â⬠¢ Blog online â⬠¢ Syllabus HW calendar coming on-line Schedule and due dates may change (check frequently) ââ¬â Lecture notes are/will be posted Other Rails Resources â⬠¢ Rails API: http://api. rubyonrails. org â⬠¢ Online tutorials ââ¬â E. g. http://poignantguide. net/ruby ââ¬â Screencasts: http://www. rubyonrails. org/screencasts ââ¬â Armando Foxââ¬â¢s daylong seminar: http://webcast. berkeley. edu/event_details. php? we bcastid=20854 â⬠¢ HW 0 posted due Friday night! ââ¬â Accts forms! â⬠¢ Other textbooks ââ¬â Korth/Silberschatz/Sudarshan ââ¬â Oââ¬â¢Neil and Oââ¬â¢Neil ââ¬â Garcia-Molina/Ullman/Widom â⬠¢ There are tons of support materials and fora on the web for RoR Relational Database: Definitions Relational database: a set of relations. â⬠¢ Relation: made up of 2 parts: ââ¬â Schema : specifies name of relation, plus name and type of each column. â⬠¢ E. g. Students(sid: string, name: string, login: string, age: integer, gpa: real) Ex: Instance of Students Relation sid 53666 53688 53650 name login Jones [emailprotected] Smith [emailprotected] Smith [emailprotected] age 18 18 19 gpa 3. 4 3. 2 3. 8 ââ¬â Instance : a table, with rows and columns. â⬠¢ #rows = cardinality â⬠¢ #fields = degree / arity â⬠¢ Cardinality = 3, arity = 5 , all rows distinc t â⬠¢ Do all values in each column of a relation instance have to be distinct? Can think of a relation as a set of rows or tuples. ââ¬â i. e. , all rows are distinct SQL A language for Relational DBs â⬠¢ SQL (a. k. a. ââ¬Å"Sequelâ⬠), standard language â⬠¢ Data Definition Language (DDL) ââ¬â create, modify, delete relations ââ¬â specify constraints ââ¬â administer users, security, etc. SQL Overview â⬠¢ CREATE TABLE ( , â⬠¦ ) â⬠¢ INSERT INTO () VALUES () â⬠¢ DELETE FROM WHERE â⬠¢ UPDATE SET = WHERE â⬠¢ SELECT FROM WHERE â⬠¢ Data Manipulation Language (DML) ââ¬â Specify queries to find tuples that satisfy criteria ââ¬â add, modify, remove tuples Creating Relations in SQL â⬠¢ Creates the Students relation. Note: the type (domain) of each field is specified, and enforced by the DBMS whenever tuples are added or modified. CREATE TABLE Students (sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpa FLOAT) Table Creation (continued) â⬠¢ Another example: the Enrolled table holds information about courses students take. CREATE TABLE Enrolled (sid CHAR(20), cid CHAR(20), grade CHAR(2)) Adding and Deleting Tuples â⬠¢ Can insert a single tuple using: INSERT INTO Students (sid, name, login, age, gpa) VALUES (ââ¬Ë53688ââ¬â¢, ââ¬ËSmithââ¬â¢, ââ¬Ë[emailprotected]ââ¬â¢, 18, 3. 2) â⬠¢ Keys Keys are a way to associate tuples in different relations â⬠¢ Keys are one form of integrity constraint (IC) Enrolled sid 53666 53666 53650 53666 cid grade Carnatic101 C Reggae203 B Topology112 A History105 B Can delete all tuples satisfying some condition (e. g. , name = Smith): Students sid 53666 53688 53650 name login Jones [emailprotected] Smith [emailprotected] Smith [emailprotected] age 18 18 19 gpa 3. 4 3. 2 3. 8 DELETE FROM Students S WHERE S. name = ââ¬ËSmithââ¬â¢ Powerful variants of these commands are available; more later! FOREIGN Key PRIMARY Key Primary Keys â⬠¢ A set of fields is a superkey if: No two distinct tuples can have same values in all key fields Primary and Candidate Keys in SQL â⬠¢ Possibly many candidate keys (specified using UNIQUE), one of which is chosen as the primary key. â⬠¢ â⬠¢ â⬠¢ A set of fields is a key for a relation if : ââ¬â It is a superkey ââ¬â No subset of the fields is a superkey Keys must be used carefully! ââ¬Å"For a given student and course, there is a single grade. â⬠CREATE TABLE Enrolled (sid CHAR(20) vs. cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid), UNIQUE (cid, grade)) â⬠¢ what if gt;1 key for a relation? ââ¬â One of the keys is chosen (by DBA) to be the primary key. Other keys are called candidate keys. â⬠¢ E. g. ââ¬â sid is a key for Students. ââ¬â What about name? ââ¬â The set {sid, gpa} is a superkey. CREATE TABLE Enrolled (sid CHAR(20) cid CHAR(20), grade CHAR(2), PRIMARY KEY (sid,cid)) ââ¬Å"Students can take only one course, and no two students in a course receive the same grade. â⬠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 the primary key of the other relation. ââ¬â Like a `logical pointerââ¬â¢. Foreign Keys in SQL â⬠¢ E. g. Only students listed in the Students relation should be allowed to enroll for courses. ââ¬â sid is a foreign key referring to Students: CREATE TABLE Enrolled (sid CHAR(20),cid CHAR(20),grade CHAR(2), PRIMARY KEY (sid,cid), FOREIGN KEY (sid) REFERENCES Students ) Enrolled sid 53666 53666 53650 53666 cid grade Carnatic101 C Reggae203 B Topology112 A History105 B â⬠¢ If all foreign key constraints are enforced, referential integrity is achieved (i. e. , no dangling references. ) sid 53666 53688 53650 Students name login Jones [emailprotected] Smith [emailprotected] Smith [emailprotected] age 18 18 19 gpa 3. 4 3. 2 3. 8 11111 English102 A
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.