CS 405G: Introduction to Database Systems

CS 405G Introduction To Database Systems-ppt Download

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

Share Presentation : CS 405G Introduction To Database Systems

Download and Preview : 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

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

27 Views0 Downloads

Database Security with focus on Hyperion Database

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.

23 Views0 Downloads

Introduction to Database Systems

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.

29 Views0 Downloads

CS186 Introduction to Database Systems

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 ...

21 Views0 Downloads

Introduction to Database Systems EPFL

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 ...

35 Views0 Downloads

Chapter 1 Introduction Database System Concepts

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.

37 Views0 Downloads

Introduction to Database System

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 ...

23 Views0 Downloads

Introduction to Relational Databases Database Design Issues

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 ...

32 Views0 Downloads

Introduction to Database Gonzaga University

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.

20 Views0 Downloads

Introduction to Database Systems Hofstra University

Another common test is to create a new file named test.php and put it in document root directory The content of this file is: <?php phpinfo(); ?> Installing PHP phpinfo() is the infamous PHP function which will spit out all kinds of stuff about PHP and your server configuration.

16 Views0 Downloads

A Practical Introduction to Database Modeling and Design

information. Data (plural) or datum (singular) are individual items that can be combined in context to form information. The first example above could represent anything that has an integer, a string, and a date field. A few examples might be a measurement and description of a particular tree taken on a specific date. ... A Practical ...

6 Views0 Downloads

Introduction to Database Systems WPI

Introduction to Database Systems Subject: Database Management Systems Author: Raghu Ramakrishnan and Johannes Gehrke Keywords: Chapter 1 Description: See the notes for information on how the slides are organized. Last modified by: default Created Date: 1/6/1997 6:13:42 PM Document presentation format: On-screen Show (4:3) Other titles

9 Views0 Downloads