CMSC424 Database Design Instructor Amol Deshpande amol cs umd edu Today Views vs Tables Integrity Constraints Continued Relational Database Design Views vs Tables Creating Create view V as select from A B where Create table T as select from A B where Can be used In any select query Only some update queries It s a new table You can do what you want Maintained as 1 Evaluate the query and store it on disk as if a table 2 Don t store Substitute in queries when referenced It s a new table Stored on disk What if a tuple inserted in A 1 If stored on disk the stored table is automatically updated to be accurate 2 If we are just substituting there is no need to do anything T is a separate table there is no reason why DBMS should keep it updated If you want that you must define a trigger Views vs Tables Views strictly supercede create a table and define a trigger to keep it updated Two main reasons for using them Security authorization Ease of writing queries E g Collaborators table if you were asked to write a lot of queries about it The way we are doing it the collaborators table is an instance of creating table and not creating view Creating a view might have been better Perhaps the only reason to create a table is to force the DBMS to choose the option of materializing That has efficiency advantages in some cases Especially if the underlying tables don t change Referential Integrity Constraints Idea prevent dangling tuples e g a loan with a bname Kenmore when no Kenmore tuple in branch Referencing Relation e g loan foreign key bname Referenced Relation e g branch primary key bname Ref Integrity ensure that foreign key value primary key value note need not to ensure i e not all branches have to have loans Referential Integrity Constraints bname Referencing Relation e g loan x x bname x Referenced Relation e g branch In SQL CREATE TABLE branch bname CHAR 15 PRIMARY KEY CREATE TABLE loan FOREIGN KEY bname REFERENCES branch Affects 1 Insertions updates of referencing relation 2 Deletions updates of referenced relation Referential Integrity Constraints c ti x tj x c x A B what happens when we try to delete this tuple Ans 3 possibilities 1 reject deletion update 2 set ti c tj c NULL 3 propagate deletion update DELETE delete ti tj UPDATE set ti c tj c to updated values Referential Integrity Constraints c ti x tj x A c x B CREATE TABLE A FOREIGN KEY c REFERENCES B action Action what happens when we try to delete this tuple 1 left blank deletion update rejected 2 ON DELETE SET NULL ON UPDATE SET NULL sets ti c NULL tj c NULL 3 ON DELETE CASCADE deletes ti tj ON UPDATE CASCADE sets ti c tj c to new key values Global Constraints Idea two kinds 1 single relation constraints spans multiple columns E g CHECK total svngs check declared in the CREATE TABLE 2 multiple relations CREATE ASSERTION SQL examples 1 single relation All Bkln branches must have assets 5M Affects CREATE TABLE branch bcity CHAR 15 assets INT CHECK NOT bcity Bkln OR assets 5M insertions into branch updates of bcity or assets in branch Global Constraints SQL example 2 Multiple relations every loan has a borrower with a savings account CHECK NOT EXISTS SELECT FROM loan AS L WHERE NOT EXISTS SELECT FROM borrower B depositor D account A WHERE B cname D cname AND D acct no A acct no AND L lno B lno Problem Where to put this constraint At depositor Loan Ans None of the above CREATE ASSERTION loan constraint CHECK Checked with EVERY DB update very expensive Summary Integrity Constraints Constraint Type Where declared Affects Expense Key Constraints CREATE TABLE Insertions Updates Moderate Insertions Updates Cheap 1 Insertions into referencing rel n 1 2 like key constraints Another reason to index sort on the primary keys PRIMARY KEY UNIQUE Attribute Constraints CREATE TABLE CREATE DOMAIN Referential Integrity Not NULL CHECK Table Tag FOREIGN KEY REFERENCES Global Constraints Table Tag CHECK or outside table CREATE ASSERTION 2 Updates of referencing rel n of relevant attrs 3 4 depends on 3 Deletions from referenced rel n a update delete policy chosen 4 Update of referenced rel n 1 For single rel n constraint with insertion deletion of relevant attrs b existence of indexes on foreign key 2 For assesrtions w every db modification 1 cheap 2 very expensive SQL Is that it Unfortunately No SQL 3 standard is several hundreds of pages if not several thousands And expensive too We will discuss a few more constructs along the way E g Embedded SQL creating indexes etc Again this is what the reference books are for you just need to know where to look in the reference book Questions Next Relational Database Design Relational Database Design Where did we come up with the schema that we used E g why not store the actor names with movies Or store the author names with the papers Topics Formal definition of what it means to be a good schema How to achieve it Movies Database Schema Movie title year length inColor studioName producerC StarsIn movieTitle movieYear starName MovieStar name address gender birthdate MovieExec name address cert netWorth Studio name address presC Changed to Movie title year length inColor studioName producerC starName merged into above MovieStar name address gender birthdate MovieExec name address cert netWorth Studio name address presC Example Relation Movie title year length inColor studioName producerC starName merged into above MovieStar name address gender birthdate MovieExec name address cert netWorth Studio name address presC Title Year Length StudioName prodC StarName Star wars 1977 120 Fox 128 Hamill Star wars 1977 120 Fox 128 Fisher Star wars 1977 120 Fox 128 H Ford King Kong 2005 Studio A 150 Naomi King Kong 1940 Studio B 20 Faye
View Full Document
Unlocking...