Review Sheet CMPSCI 445 at UMass
Review Sheet CMPSCI 445 at UMass
Popular in Course
Popular in Department
This 26 page Class Notes was uploaded by an elite notetaker on Friday February 6, 2015. The Class Notes belongs to a course at University of Massachusetts taught by a professor in Fall. Since its upload, it has received 10 views.
Reviews for Review Sheet CMPSCI 445 at UMass
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 02/06/15
Querying Relational Data SQL CMPSCI 445 Fall 2008 Review of Joins Today Conclude relational algebra Begin SQL Review of Joins Joins are the most common way to combine information from two tables Theta Join R1gtlt6R2 06R1XR2 Equijoin R1 lgtltl AB R2 GAB R1 X R2 Natural Join R1lgtltl R2 HAGCR1 X R2 Example Database STUDENT Takes COURSE 1 Jill 1 445 445 DB F08 B0 1 483 483 Al 808 Maya 3 435 435 Arch F08 PROFESSOR Teaches 1 445 1 Diao Saul 2 483 Weems 8 435 Natural join questions Given the schemas RA B C D SA C E what is the schema of R IgtltIS Given RA B C SD E what is RIgtltS Given RA B SA B What is RIgtltS Algebraic Equivalences Relational algebra has laws of commutativity associativity etc that imply certain expressions are equivalent OCAdR E 0c MR cascading selection R X S X T E R X s X T join associativity OCR X S E 00R X s pushing selections We can use these equivalences to generate equivalent operator trees Expression tree Hnamesid Otite DBquot Course gtltl Students lgtlt1Takes IIn am e 5 id Otitle DB lgtlt iasx m W eff MM 9 s Course Students Takes Algebra v Calculus Relational Algebra More operational very useful for representing execution plans Relational Calculus More declarative basis of SQL The calculus and algebra have equivalent expressive power Codd A language that can express this core class of queries is called Relationally Complete Relational Algebra amp Calculus can t express all queries Tuples in FLIGHTS represent direct flights FL39HTS 7 from departure city to H m WW arrival city NYC R What about connecting eno flights NYC Oakland A selfjoin is a join of a Boston Tampa table with itself RA RC can t express oakland Boston repeated joins Tampa NYC Next SQL SQL Overview SQL Preliminaries Nested queries Integrity constraints Correlat39on Null values Query capabilities M df h d b SELECTFROM o iyingt e ata ase WHERE blocks Views Basic features ordering duplicates Set ops union intersect except Aggregation amp Grouping Review in the textbook Ch 5 The SQL Query Language Structured Query Language Developed by IBM system R in the 1970s Need fora standard since it is used by many vendors Evolving standard SOL86 SOL89 minor revision SOL92 major revision SQL99 major extensions SOL2003 minor revisions Two parts of SQL Data Definition Language DDL Createaterdeete tables and their attributes estabish and modify schema Data Manipulation Language DML Query and modify database instance Creating Relations in SQL Creates the Student relation CREATE TABLE Student Observe that the type domain sid CHARQO of each field is specified and name CHARQO enforced by the DBMS loglnCHAR1039 age INTEGER whenever tuples are added or a REAL modified gp As another example the I CREATE TABLE Takes Takes table holds Information Sid CHARQO about courses that students Cid CHARQO take grade CHAR2 Characters CHAR20 Numbers MONEY VARCHAR40 Data Types in SQL fixed length variable length BIGINT INT SMALLINT TINYINT REAL FLOAT differ in precision Times and dates DATE DATETIME Others Destroying and Altering Relations DROP TABLE Student Destroys the relation Student The schema information and the tuples are deleted ALTER TABLE Student ADD COLUMN firstYear integer The schema of Student is altered by adding a new field every tuple in the current instance is extended with a null value in the new field Integrity Constraints le IC condition that must be true for any instance of the database le are specified when schema is defined le are checked when relations are modified A legal instance of a relation is one that satisfies all specified le DBMS should only allow legal instances If the DBMS checks le stored data is more faithful to realworld meaning Avoids data entry errors too Key Constraints A set of fields is a key for 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 If part 2 false then fields are a superkey If there s more than one key for a relation one of the keys is chosen by DBA to be the primary key Eg sid is a key for Students What about name The set sid gpa is a superkey Student table STUDENT 50000 Dave davecs 19 32 53666 Jones jonescs 18 33 53688 Smith smithee 18 32 53650 Smith smithmath 19 37 53831 Madayan madayanmusic 11 18 53832 Guldu guldumusic 12 20 19 Specifying Key Constraints in SQL CREATE TABLE Student Sid CHAR20 name CHAR20 login CHAR10 age INTEGER gpa REAL UNIQUE name age PRIMARY KEY Sid Possibly many candidate keys specified using UNIQUE one of which is chosen as the primary key 20 Primary and Candidate Keys in SQL CREATE TABLE Takes sid CHAR20 Cid CHARQO For a given student and course there grade CHARQ is a single grade PRIMARY KEY sidcid CREATE TABLE Takes Sig CHARQO Students can take only one course and Cld CHARQO receive a single grade for that course grade CHAR2 further no two students in a course pRIMARY KEY Sid receive the same grade UNIQUE Cid grade Used carelessly an I C can prevent the storage of database instances that arise in practice 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 Eg sid is a foreign key referring to Students Takessid string Cid string grade string If all foreign key constraints are enforced referential intearitv is achieved ie no dangling references Can you name a data model wo referential integrity Links in HTML Foreign Keys in SQL Only students listed in the Students relation should be allowed to enroll for courses CREATE TABLE Takes sid CHAR20 cid CHAR20 grade CHAR2 PRIMARY KEY sidCid FOREIGN KEY sid REFERENCES Students STUDENT Takes 50000 Dave davecs 50000 445 A 53666 Jones jonescs 74 53688 Smith smithee 53666 435 B 53688 483 O 53650 Smith smithmath nforoing Referential Integrity o Consider Student and Takes sid in Takes is a foreign key that references Student What should be done if a Takes tuple with a nonexistent student id is inserted Reject it What should be done if a Student tuple is deleted Also delete all Takes tuples that refer to it Disallow deletion of a Students tuple that is referred to Set sid in Takes tuples that refer to it to a default sid In SQL also Set sid in Takes tuples that refer to it to a special value null denoting unknown or inapplicable Similar if primary key of Students tuple is updated Referential Integrity in SQL SOL92 and SQL1999 support all CREATE TABLE Takes 4 options on deletes and updates Sid CHARQO Default is NO ACTION delete update is rejected CASCADE also delete all tuples that refer to deleted cid CHAR20 grade CHAR2 PRIMARY KEY sidCid tuple FOREIGN KEY sid SET NULL SET DEFAULT sets REFERENCES Students foreign key value of ON DELETE CASCADE referencing tUIDIe ON UPDATE SET DEFAULT Where do le Come From le are based upon the semantics of the realworld 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 le are the most common more general le supported too
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'