• Date:29 Jul 2020
• Views:16
• Size:611.50 KB

Share Presentation : CS 405G Introduction To Database Systems

Report CopyRight/DMCA Form For : CS 405G Introduction To Database Systems

Transcription:

CS 405G Introduction toDatabase Systems Summary of SQL featurescovered so far SELECT FROM WHERE statements.
Ordering Set and bag operations Aggregation and grouping Nested queries Next NULL s outerjoins data modification constraints .
07 29 20 Jinze Liu University of Kentucky 2 Incomplete information Example Student SID name age GPA Value unknown We do not know Nelson s age.
Value not applicable Nelson has not taken any classes yet what is his GPA 07 29 20 Jinze Liu University of Kentucky 3 Solution 1 A dedicated special value for each domain type .
GPA cannot be 1 so use 1 as a special value to indicatea missing or invalid GPA Leads to incorrect answers if not careful SELECT AVG GPA FROM Student Complicates applications.
SELECT AVG GPA FROM StudentWHERE GPA 1 Remember the Y2K bug 00 was used as a missing or invalid year value07 29 20 Jinze Liu University of Kentucky 4.
Solution 2 A valid bit for every column Student SID name name is valid age age is valid GPA GPA is valid .
Complicates schema and queries SELECT AVG GPA FROM StudentWHERE GPA is valid 07 29 20 Jinze Liu University of Kentucky 5 Solution 3 .
Decompose the table missing row missing value StudentName SID name StudentAge SID age StudentGPA SID GPA StudentID SID .
Conceptually the cleanest solution Still complicates schema and queries How to get all information about a student in a table Would natural join work 07 29 20 Jinze Liu University of Kentucky 6.
SQL s solution A special value NULL For every domain Special rules for dealing with NULL s Example Student SID name age GPA .
h 789 Nelson NULL NULL i07 29 20 Jinze Liu University of Kentucky 7 Three valued logic TRUE 1 FALSE 0 UNKNOWN 0 5 x AND y min x y .
x OR y max x y NOT x 1 xAND True False NULL OR True False NULLTrue True False UNK True True False TRUEFalse False False False False False False UNK.
NULL UNK False UNK NULL TRUE UNK UNK WHERE and HAVING clauses only select rows for output if thecondition evaluates to TRUE UNKNOWN is not enough07 29 20 Jinze Liu University of Kentucky 8.
Computing with NULL s Arithmetic operation when we operate on a NULL andanother value including another NULL using etc the result is NULL Aggregate functions ignore NULL except COUNT .
since it counts rows When we compare a NULL with another value including another NULL using etc the result is07 29 20 Jinze Liu University of Kentucky 9 Unfortunate consequences.
SELECT AVG GPA FROM Student SELECT SUM GPA COUNT FROM SELECT FROM Student SELECT FROM Student WHERE GPA GPAsid name age gpa.
Not equivalent 1234 John Smith 21 3 51123 Mary Carter 19 3 81011 Bob Lee 22 NULL1204 Susan Wong 22 3 41306 Kevin Kim 18 2 9.
07 29 20 Jinze Liu University of Kentucky 10 Another problem Example Who has NULL GPA values SELECT FROM Student WHERE GPA NULL Does not work never returns anything.
SELECT FROM Student EXCEPT ALL SELECT FROM Student WHERE GPA GPA Works but ugly Introduced built in predicates IS NULL and IS NOT.
SELECT FROM Student WHERE GPA IS NULL 07 29 20 Jinze Liu University of Kentucky 11 Outerjoin motivation Example a master class list SELECT c CID s SID.
FROM Enroll e Student s Course cWHERE e SID s SID and c CID e CID What if a student take no classes For these students CID column should be NULL What if a course with no student enrolled yet .
For these courses SID should be NULL07 29 20 Jinze Liu University of Kentucky 12 Outerjoin SELECT FROM R FULL OUTER JOIN S ON p A full outer join between R and S denoted R S .
includes all rows in the result of R pS plus Dangling R rows those that do not join with any Srows padded with NULL s for S s columns Dangling S rows those that do not join with any Rrows padded with NULL s for R s columns.
07 29 20 Jinze Liu University of Kentucky 13 Outerjoin II SELECT FROM R LEFT OUTER JOIN S ON p SELECT FROM R RIGHT OUTER JOIN S ON p A left outer join R S includes rows in R S plus dangling R.
rows padded with NULL s A right outer join R S includes rows in R S plus dangling Srows padded with NULL s07 29 20 Jinze Liu University of Kentucky 14 Outerjoin examples.
Employee SELECT FROM Employee LEFT OUTERJOIN Department ON Eid MidEid Name Eid Name Did Mid Dname1123 John Smith 1123 John Smith 4 1123 Research1234 Mary Carter 1234 Mary Carter 5 1234 Finance.
1311 Bob Lee 1311 Bob Lee NULL NULL NULLSELECT FROM Employee FULLRIGHTOUTERDepartment JOIN Department ON Eid MidDid Mid Dname Eid Name Did Mid Dname.
4 1123 Research 1123 John Smith 4 1123 Research5 1234 Finance 1234 Mary Carter 5 1234 Finance6 1312 HR NULLNULL NULL 6 1312 HR07 29 20 Jinze Liu University of Kentucky 15.
SELECT FROM WHERE statements Ordering Set and bag operations Aggregation and grouping Table expressions subqueries.
Outerjoins07 29 20 Jinze Liu University of Kentucky 16 Summary of SQL featurescovered so far SELECT FROM WHERE statements.
Set and bag operations Ordering Aggregation and grouping Table expressions subqueries NULL s and outerjoins.
Next data modification statements constraints07 29 20 Jinze Liu University of Kentucky 17CS 405G: Introduction to Database Systems ... SQLâ€™s solution Three-valued logic Computing with NULLâ€™s Unfortunate consequences Another problem Outerjoin motivation Outerjoin Outerjoin (II) Outerjoin examples Summary Summary of SQL features covered so far ...

### Related Presentations

Database Models: Flat Files and the Relational Database Objectives: Understand the fundamental structure of the relational database model Learn the circumstances under which it is a better choice than the flat file

Hyperion application security determines user access to products using the concept of roles. A role is a set of permissions that determines user access to product functions. User directories store information about the users who can access Hyperion products. Both the Authentication and the authorization processes utilize user information.

Database design: logical design---select suitable data model physical design---select suitable indexing d. Access management design: how to serve large-scale users? e. Efficiency concern: how to manage large-scale data set? Ramakrishnan & Gehrke Summary DBMS used to maintain, query large datasets.

Title: CS186: Introduction to Database Systems Last modified by: Joe Hellerstein Document presentation format: On-screen Show Other titles: Arial Times New Roman Tahoma Times Book Antiqua Osaka Bradley Hand ITC TT-Bold Lucida Sans Unicode Symbol Monotype Sorts lecture1.key CS186: Introduction to Database Systems Queries for Today What: Database Systems Then What: Database Systems Today What ...

Introduction to Information Systems SSC, Semester 6 Lecture 01 Staff Instructors: Karl Aberer, BC 108, karl aberer at epfl ch Philippe CudrÃ©-Mauroux, BC 114, philippe cudre-mauroux at epfl ch Office hours: by appointment TAs: Gleb Skobeltsyn (exercises) Martin Rubli (project) Communications Web page: lsirww.epfl.ch Lectures will be available here Homeworks and solutions will be posted here ...

Chapter 2: Intro to Relational Model * * * * * * * * * * * * Example of a Relation attributes (or columns) tuples (or rows) Attribute Types The set of allowed values for each attribute is called the domain of the attribute Attribute values are (normally) required to be atomic; that is, indivisible The special value null is a member of every domain.

Silberschatz, Korth, Sudarshan: Database System Concepts, McGraw Hill, 5th Edition, 2006 COMP-421: Database Systems Khaled Jababo [email protected] Names and Numbers Class: Monday, Wednesday, Friday 13:35-14:25 Lecturer: Khaled Jababo, McConnell Eng. Bldg. Room TBD [email protected] Office Hours: By Appointment Marking Scheme Marking Scheme ...

Owen Olga Owen Organiser C1 C2 C1 CourseId 13 24 13 Room S2 S1 S1 StudentId SCOR Assume FDs: { CourseId } â†’ { Organiser } { Organiser } â†’ { Room } { Room } â†’ { Organiser } Which one do we address first? 13 24 13 Room C1 C2 C1 CourseId S2 S1 S1 StudentId SCR â€œLosesâ€ { Room } â†’ { Organiser } and {Organiser } â†’ { Room } CO Olga C2 ...

SOLUTION: Central repository of shared data. ... Database Management System (DBMS) â€“software for managing the database. Database ... Figure 1-12 Three-tiered client/server database architecture. Chapter 1. Muti/N-tier: Web-Enabled Databases. Web applications requiring databases.