Database Systems Application Usage 1 2 Step 0 Analyze Step 1 - PDF document

2011-03-03 Creating a Database System Design Construction Database Systems Application Usage 1 2 Step 0 – Analyze Step 1 – Design • Analyze the domain • Design the database – Learn what things the system is intended to – Create a schema that captures the constraints handle. of the domain. – Learn what the constraints of the domain are. • E-R diagram, translated to a set of relation schemas. • Analyze the system – Note any constraints that are not directly – Learn what the system is intended to do, what captured by the structure of the schema. operations it should perform. • Value constraints. • Multiplicity constraints. • We’ve partly done part of this for you… • Broken dependencies. 3 4 Course Objectives – Design The Relational Data Model • Relations are sets of tuples. When the course is through, you should – Tuples are sets of data. – All tuples in a relation have the same set of attributes – Given a domain, know how to design a with values. database that correctly models the domain – A database schema is a set of interconnected relation and its constraints. schemas. • Relations are a very simple model, mapping well ”We want a database that we can use for to how we tend to ”think” about data. scheduling courses and lectures. This is • Most DBMS of today use a relational model. how it’s supposed to work: …” 5 6 1

2011-03-03 The Entity-Relationship approach E-R diagram for Scheduler • Design your database by drawing a picture #students period code of it – an Entity-Relationship diagram. Course Given GivenCourse teacher name – Allows us to sketch the design of a database informally (which is good when communi- cating with customers). Of • Use (more or less) mechanical methods to Room Lecture convert your diagram to relations. name In weekday – This means that the diagram can be a formal time #seats specification as well. 7 8 Translate to relations E-R summary • Entities, attributes Courses(code, name) GivenCourses(course, period, #students, teacher) • Relationships, multiplicity course -> Courses.code Lectures(course, period, room, weekday, hour) • Weak entities (course, period) -> GivenCourses.(course, period) room -> Rooms.name • Subclassing (ISA) Rooms(name, #seats) • Translation to relations 9 10 Step 1b – Verification Functional dependencies (FDs) • X � A • Once you have a schema, verify that it is correct with respect to the constraints of the domain! – ”X determines A”, ”X gives A” – Functional dependencies, independencies – ”A depends on X” • For the constraints found in step 1, show that • X is a set of attributes, A is a single your design captures the constraint. attribute. – For those constraints that are not captured, argue why not. • Examples: – code � name • Anyone can make a schema, not everyone can – code, period � teacher make a correct schema… 11 12 2

2011-03-03 Quiz! Anomalies Courses(code, period, name, teacher) What’s the point of functional dependencies? Why are E-R diagrams not enough? • Redundancy – Same course name stored several times. – E-R diagrams can be wrong. – Redundancy in a schema leads to possible anomalies. • Bad design – you’ve thought wrong when doing the diagram. • Bad translation – something went wrong when translating to • Update anomaly relations. – If we update the course name, we must remember to – Dependencies are an extra safe-guard against errors, update all tuples. as well as a formal proof that your design is correct. • Deletion anomaly – Dependencies represent ”extra” constraints that your – If a course is not scheduled to be given in any period diagram cannot handle. this year, we lose its name! 13 14 Using FDs to detect redundancy Decomposition Courses(code, period, name, teacher) • Whenever X � A holds for a relation R, code � name code, period � teacher but X is not a key for R, then values of A • Fix the problem by decomposing Courses: will be redundantly repeated! – Create one relation with the attributes from the offending FD, in this case code and name . Courses(code, period, name, teacher) – Keep the original relation, but remove all attributes from the RHS of the FD. Insert a reference from the LHS in this relation, to the {(’TDA356’, 2, ’Databases’, ’Niklas Broberg’), key in the first. (’TDA356’, 4, ’Databases’, ’Rogardt Heldal’)} Courses(code, name) code � name code, period � teacher GivenCourses(code, period, teacher) code -> Courses.code 15 16 Boyce-Codd Normal Form Quiz! • A relation R is in Boyce-Codd Normal Why not use BCNF decomposition for designing database schemas? Why go via E-R Form (BCNF) if, whenever a nontrivial FD diagrams? X � A holds on R, X is a superkey of R. – Decomposition doesn’t handle all situations – Remember: nontrivial means A is not part of X gracefully. E.g. – Remember: a superkey is any superset of a • Self-relationships key (including the keys themselves). • Many-to-one vs. many-to-”exactly one” • Subclasses • Single-attribute entities Courses(code, name) – E-R diagrams are graphical, hence easier to sell GivenCourses(code, period, teacher) than some mathematical formulae. 17 18 3

2011-03-03 Other normal forms Properties of normalization • BCNF decomposition guarantees 3NF is a relaxation of BCNF – Lossless join – Accept a relation even if there are dependencies X � A where X is not a – No redundancy (in most cases) superkey, as long as A is in some key (prime). • 3NF decomposition guarantees • 4NF is a strengthening of BCNF – Lossless join – Dependencies are preserved – Requires us to handle not only FDs, but also • 4NF decomposition guarantees independencies (INDs). – Lossless join – No redundancy 19 20 Summary – dependencies and Summary – Design normal forms • Functional dependencies (FDs) • Model the domain as an E-R diagram. – X � A, ”X determines A”, ”A depends on X” – Trivial FDs, computing closures (X + , F + ), finding all implied FDs of a domain. • Translate the diagram to a set of relation • Independencies (INDs) schemas, forming a schema for the – X ↠ Y | Z, ”Y and Z are independent with respect to database. X” – X ↠ Y, ”Y is independent from everything else w.r.t X” • Normal Forms • Verify using dependencies and normal – BCNF, 3NF, 4NF forms that your design is correct. • Normalization algorithms 21 22 Creating a Database System Step 2 – Construction • Implement the database schema in a DBMS. Design – Relations become tables, attributes become Construction columns. – Constraints on relations. • Primary keys • References, foreign keys Application • Value constraints (checks) Usage • General constraints (assertions) 23 24 4

2011-03-03 Course Objectives – Construction SQL Data Definition Language • CREATE TABLE , attributes When the course is through, you should • Constraints (name them!) – PRIMARY KEY – Given a database schema with related – FOREIGN KEY … REFERENCES constraints, implement the database in a – CHECK relational (SQL) DBMS. • Tuple-based • Attribute-based • Assertions, CREATE ASSERTION – SQL Data Definition Language • Views, CREATE VIEW – Persistent queries – Virtual tables 25 26 Step 2b – Help constructs Triggers • Implement auxiliary functionality around • Triggers are actions to perform when the core database (the tables) in order to some event occurs. simplify the application. – Events: insertion, deletion, update – Triggers to ensure consistency and simplify – Condition: test whether to run the action or complex modifications. not. – Views to simplify complex queries (and – Action: The stuff to actually do. modifications) and present a proper interface. – Privileges to ensure that things are done A trigger is sometimes referred to as an ECA – correctly. Event-Condition-Action 27 28 Basic trigger structure Views • Views can be seen in two ways: CREATE TRIGGER name – Persistent queries. [BEFORE|AFTER] [INSERT|DELETE|UPDATE] ON tablename • A view is a query that is given a name. The REFERENCING [NEW|OLD] [ROW|TABLE] AS variable ”contents” of the view are fetched by the query FOR EACH [ROW|STATEMENT] WHEN condition from other tables. Decide whether to run action to perform the trigger or not. – Virtual tables. • A view is a table that has no data of its own, its What should happen when ”contents” are stored in other tables. the trigger is triggered. • A query on a view is translated to the query that was used to define it. • An update on a view can be translated to updates on base tables using special instead of triggers. 29 30 5