Database Systems Database Systems 1
Creating a Database System Design Construction Application Usage 2
Step 0 – Analyze • Analyze the domain – Learn what things the system is intended to handle. – Learn what the constraints of the domain are. • Analyze the system – Learn what the system is intended to do, what operations it should perform. • We’ve partly done part of this for you… 3
Step 1 – Design • Design the database – Create a schema that captures the constraints of the domain. • E-R diagram, translated to a set of relation schemas. schemas. – Note any constraints that are not directly captured by the structure of the schema. • Value constraints. • Multiplicity constraints. • Broken dependencies. 4
Course Objectives – Design When the course is through, you should – Given a domain, know how to design a database that correctly models the domain and its constraints. ”We want a database that we can use for scheduling courses and lectures. This is how it’s supposed to work: …” 5
The Relational Data Model • Relations are sets of tuples. – Tuples are sets of data. – All tuples in a relation have the same set of attributes with values. – A database schema is a set of interconnected relation – A database schema is a set of interconnected relation schemas. • Relations are a very simple model, mapping well to how we tend to ”think” about data. • Most DBMS of today use a relational model. 6
The Entity-Relationship approach • Design your database by drawing a picture of it – an Entity-Relationship diagram. – Allows us to sketch the design of a database informally (which is good when communi- informally (which is good when communi- cating with customers). • Use (more or less) mechanical methods to convert your diagram to relations. – This means that the diagram can be a formal specification as well. 7
E-R diagram for Scheduler #students period code Course GivenCourse teacher Given name Of Room Lecture weekday name In time #seats 8
Translate to relations Courses(code, name) GivenCourses(course, period, #students, teacher) course -> Courses.code Lectures(course, period, room, weekday, hour) (course, period) -> GivenCourses.(course, period) room -> Rooms.name Rooms(name, #seats) 9
E-R summary • Entities, attributes • Relationships, multiplicity • Weak entities • Subclassing (ISA) • Translation to relations 10
Step 1b – Verification • Once you have a schema, verify that it is correct with respect to the constraints of the domain! – Functional dependencies, independencies • For the constraints found in step 1, show that • For the constraints found in step 1, show that your design captures the constraint. – For those constraints that are not captured, argue why not. • Anyone can make a schema, not everyone can make a correct schema… 11
Functional dependencies (FDs) • X � A – ”X determines A”, ”X gives A” – ”A depends on X” • X is a set of attributes, A is a single • X is a set of attributes, A is a single attribute. • Examples: – code � name – code, period � teacher 12
Quiz! What’s the point of functional dependencies? Why are E-R diagrams not enough? – E-R diagrams can be wrong. • Bad design – you’ve thought wrong when doing the diagram. • Bad design – you’ve thought wrong when doing the diagram. • Bad translation – something went wrong when translating to relations. – Dependencies are an extra safe-guard against errors, as well as a formal proof that your design is correct. – Dependencies represent ”extra” constraints that your diagram cannot handle. 13
Anomalies Courses(code, period, name, teacher) • Redundancy – Same course name stored several times. – Redundancy in a schema leads to possible anomalies. • Update anomaly – If we update the course name, we must remember to update all tuples. • Deletion anomaly – If a course is not scheduled to be given in any period this year, we lose its name! 14
Using FDs to detect redundancy • Whenever X � A holds for a relation R, but X is not a key for R, then values of A will be redundantly repeated! Courses(code, period, name, teacher) {(’TDA356’, 2, ’Databases’, ’Niklas Broberg’), (’TDA356’, 4, ’Databases’, ’Rogardt Heldal’)} code � name code, period � teacher 15
Decomposition Courses(code, period, name, teacher) code � name code, period � teacher • Fix the problem by decomposing Courses: – Create one relation with the attributes from the offending FD, in this case code and name . this case code and name . – 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 key in the first. Courses(code, name) GivenCourses(code, period, teacher) code -> Courses.code 16
Boyce-Codd Normal Form • A relation R is in Boyce-Codd Normal Form (BCNF) if, whenever a nontrivial FD X � A holds on R, X is a superkey of R. – Remember: nontrivial means A is not part of X – Remember: nontrivial means A is not part of X – Remember: a superkey is any superset of a key (including the keys themselves). Courses(code, name) GivenCourses(code, period, teacher) 17
Quiz! Why not use BCNF decomposition for designing database schemas? Why go via E-R diagrams? – Decomposition doesn’t handle all situations gracefully. E.g. • Self-relationships • Many-to-one vs. many-to-”exactly one” • Subclasses • Single-attribute entities – E-R diagrams are graphical, hence easier to sell than some mathematical formulae. 18
Other normal forms 3NF is a relaxation of BCNF – Accept a relation even if there are dependencies X � A where X is not a superkey, as long as A is in some key (prime). superkey, as long as A is in some key (prime). • 4NF is a strengthening of BCNF – Requires us to handle not only FDs, but also independencies (INDs). 19
Properties of normalization • BCNF decomposition guarantees – Lossless join – No redundancy (in most cases) • 3NF decomposition guarantees • 3NF decomposition guarantees – Lossless join – Dependencies are preserved • 4NF decomposition guarantees – Lossless join – No redundancy 20
Summary – dependencies and normal forms • Functional dependencies (FDs) – X � A, ”X determines A”, ”A depends on X” – Trivial FDs, computing closures (X + , F + ), finding all implied FDs of a domain. • Independencies (INDs) • Independencies (INDs) – X ↠ Y | Z, ”Y and Z are independent with respect to ↠ X” – X ↠ Y, ”Y is independent from everything else w.r.t X” • Normal Forms – BCNF, 3NF, 4NF • Normalization algorithms 21
Summary – Design • Model the domain as an E-R diagram. • Translate the diagram to a set of relation schemas, forming a schema for the schemas, forming a schema for the database. • Verify using dependencies and normal forms that your design is correct. 22
Creating a Database System Design Construction Application Usage 23
Step 2 – Construction • Implement the database schema in a DBMS. – Relations become tables, attributes become columns. columns. – Constraints on relations. • Primary keys • References, foreign keys • Value constraints (checks) • General constraints (assertions) 24
Course Objectives – Construction When the course is through, you should – Given a database schema with related constraints, implement the database in a constraints, implement the database in a relational (SQL) DBMS. – SQL Data Definition Language 25
SQL Data Definition Language • CREATE TABLE , attributes • Constraints (name them!) – PRIMARY KEY – FOREIGN KEY … REFERENCES – CHECK CHECK • Tuple-based • Attribute-based • Assertions, CREATE ASSERTION • Views, CREATE VIEW – Persistent queries – Virtual tables 26
Step 2b – Help constructs • Implement auxiliary functionality around the core database (the tables) in order to simplify the application. – Triggers to ensure consistency and simplify – Triggers to ensure consistency and simplify complex modifications. – Views to simplify complex queries (and modifications) and present a proper interface. – Privileges to ensure that things are done correctly. 27
Triggers • Triggers are actions to perform when some event occurs. – Events: insertion, deletion, update – Condition: test whether to run the action or – Condition: test whether to run the action or not. – Action: The stuff to actually do. A trigger is sometimes referred to as an ECA – Event-Condition-Action 28
Basic trigger structure CREATE TRIGGER name [BEFORE|AFTER] [INSERT|DELETE|UPDATE] ON tablename REFERENCING [NEW|OLD] [ROW|TABLE] AS variable FOR EACH [ROW|STATEMENT] WHEN condition WHEN condition Decide whether to run action to perform the trigger or not. What should happen when the trigger is triggered. 29
Recommend
More recommend
