relational modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfsummary • relational model – collection...
TRANSCRIPT
![Page 1: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/1.jpg)
Relational Model
Department of Computer Science and EngineeringIndian Institute of Technology Ropar
Narayanan (CK) Chatapuram Krishnan!
CSL 451 Introduction to Database Systems
![Page 2: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/2.jpg)
Summary• Relational model
– Collection of relations or tables– Schema of a relation refers its logical design– Instance of a relation refers to its contents at a certain point in time
• Schema of a relation– Attributes (atomicity)– Type of the attributes (domain)– Constraints on the relation
• Superkey: a set of one or more attributes whose values are guaranteed to identify tuples in the relation uniquely– Candidate key – minimal superkey– Primary key – the chosen candidate key of a relation– Foreign key – set of attributes in the referencing relation that constitute
the primary key in the referenced relation15/01/15! Department of Computer Science and Engineering !
Indian Institute of Technology Ropar! 2!
![Page 3: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/3.jpg)
Summary• Schema Diagram– Pictorial depiction of the schema of a database
• Relational query languages– Define a set of operations that operate on
tables(relations), and output tables(relations) as their results
• Relational Algebra– An example of the set of operations– Selection– Projection– Natural Join– Cartesian Product– Union
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 3!
![Page 4: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/4.jpg)
Example of a Schema
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 4!
![Page 5: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/5.jpg)
2.1 Consider the following relational databaseemployee (person_name, street, city)
works (person_name, company_name, salary)company (company_name, city)
What are the appropriate primary keys?
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 5!
![Page 6: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/6.jpg)
2.2 Consider the foreign key constraint from the dept_name attribute of instructor to the department relation. Give examples of inserts and deletes to these relations, which can cause a violation of the foreign key constraint
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 6!
![Page 7: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/7.jpg)
2.3 Consider the time_slot relation. Given that a particular time slot can meet more than once in a week, explain why day and start_time are part of the primary key of this relation, while end_time is not?
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 7!
![Page 8: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/8.jpg)
2.4 In the instance of instructor, no two instructors have the same name. From this can we conclude that name can be used as a superkey (or primary key) of instructor?
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 8!
![Page 9: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/9.jpg)
2.5 What is the result of first performing the cross product of student and advisor, and then performing a selection operation on the result with the predicate s_id = ID?
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 9!
![Page 10: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/10.jpg)
2.6.a Explain in words what the expression does
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 10!
�year�2009(takes) ./ student
![Page 11: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/11.jpg)
2.6.b Explain in words what the expression does
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 11!
�year�2009(takes ./ student)
![Page 12: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/12.jpg)
2.6.c Explain in words what the expression does
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 12!
⇧ID,name,course id
(student ./ takes)
![Page 13: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/13.jpg)
2.7.a Consider the following relational databaseemployee (person_name, street, city)
works (person_name, company_name, salary)company (company_name, city)
Give a relational algebra expression for – finding the names of all employees who live in city “Miami”
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 13!
![Page 14: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/14.jpg)
2.7.b Consider the following relational databaseemployee (person_name, street, city)
works (person_name, company_name, salary)company (company_name, city)
Give a relational algebra expression for – finding the names of all employees whose salary is greater than $100,000.
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 14!
![Page 15: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/15.jpg)
2.7.c Consider the following relational databaseemployee (person_name, street, city)
works (person_name, company_name, salary)company (company_name, city)
Give a relational algebra expression for – finding the names of all employees who live in “Miami” and whose salary is greater than $100,000.
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 15!
![Page 16: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/16.jpg)
2.9 Consider the following bank databasebranch (branch_name, branch_city, assets)
customer (customer_name, customer_street, customer_city)loan(loan_number, branch_name, amount)borrower (customer_name, loan_number)
account (account_number, branch_name, balance)depositor (customer_name, account_number)
a. What are the appropriate primary keys?b. Given your choice of primary keys, identify appropriate
foreign keys.
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 16!
![Page 17: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/17.jpg)
2.8 Consider the following bank databasebranch (branch_name, branch_city, assets)customer (customer_name, customer_street, customer_city)loan(loan_number, branch_name, amount)borrower (customer_name, loan_number)account (account_number, branch_name, balance)depositor (customer_name, account_number)
Give an expression in relational algebra fora. Find the names of all branches located in “Chicago”
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 17!
![Page 18: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/18.jpg)
2.8 Consider the following bank databasebranch (branch_name, branch_city, assets)customer (customer_name, customer_street, customer_city)loan(loan_number, branch_name, amount)borrower (customer_name, loan_number)account (account_number, branch_name, balance)depositor (customer_name, account_number)
Give an expression in relational algebra forb. Find the names of all borrowers who have a loan in branch
“Downtown”
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 18!
![Page 19: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/19.jpg)
2.13 Consider the following bank databasebranch (branch_name, branch_city, assets)customer (customer_name, customer_street, customer_city)loan(loan_number, branch_name, amount)borrower (customer_name, loan_number)account (account_number, branch_name, balance)depositor (customer_name, account_number)
Give an expression in relational algebra fora. Find all loan numbers with a loan value greater than $10,000
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 19!
![Page 20: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/20.jpg)
2.13 Consider the following bank databasebranch (branch_name, branch_city, assets)customer (customer_name, customer_street, customer_city)loan(loan_number, branch_name, amount)borrower (customer_name, loan_number)account (account_number, branch_name, balance)depositor (customer_name, account_number)
Give an expression in relational algebra forb. Find the names of all depositors who have an account with a
value greater than $6,000
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 20!
![Page 21: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/21.jpg)
2.13 Consider the following bank databasebranch (branch_name, branch_city, assets)customer (customer_name, customer_street, customer_city)loan(loan_number, branch_name, amount)borrower (customer_name, loan_number)account (account_number, branch_name, balance)depositor (customer_name, account_number)
Give an expression in relational algebra forc. Find the names of all depositors who have an account with a
values greater than $6,000 at the “Uptown” branch.
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 21!
![Page 22: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/22.jpg)
2.10 Consider the advisor relation, with s_id as the primary key of advisor. Suppose a student can have more than one advisor. Then, would s_id still be a primary key of the advisor relation? If not, what should the primary key of advisor be?
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 22!
![Page 23: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/23.jpg)
2.11 Describe the differences in the meaning between the terms relation and relation schema
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 23!
![Page 24: Relational Modelcse.iitrpr.ac.in/ckn/courses/s2015/w1.pdfSummary • Relational model – Collection of relations or tables – Schema of a relation refers its logical design – Instance](https://reader031.vdocuments.us/reader031/viewer/2022040916/5e8e6909f3c1100680741fa2/html5/thumbnails/24.jpg)
2.14 List two reasons why null values might be introduced into the database
15/01/15! Department of Computer Science and Engineering !Indian Institute of Technology Ropar! 24!