The document contains two tests with multiple questions about relational databases and relational algebra. Test 1 asks about differentiating attribute types, Armstrong's axioms for functional dependencies, proving the Armstrong's union rule and BCNF properties, and writing relational algebra queries over a database schema for university courseware. Test 2 asks about determining if a relation decomposition is lossless based on functional dependencies, writing relational algebra and tuple relational calculus queries over a supplier database schema, and writing SQL queries involving views and joins over customer, item, and order relations.
The document contains two tests with multiple questions about relational databases and relational algebra. Test 1 asks about differentiating attribute types, Armstrong's axioms for functional dependencies, proving the Armstrong's union rule and BCNF properties, and writing relational algebra queries over a database schema for university courseware. Test 2 asks about determining if a relation decomposition is lossless based on functional dependencies, writing relational algebra and tuple relational calculus queries over a supplier database schema, and writing SQL queries involving views and joins over customer, item, and order relations.
The document contains two tests with multiple questions about relational databases and relational algebra. Test 1 asks about differentiating attribute types, Armstrong's axioms for functional dependencies, proving the Armstrong's union rule and BCNF properties, and writing relational algebra queries over a database schema for university courseware. Test 2 asks about determining if a relation decomposition is lossless based on functional dependencies, writing relational algebra and tuple relational calculus queries over a supplier database schema, and writing SQL queries involving views and joins over customer, item, and order relations.
The document contains two tests with multiple questions about relational databases and relational algebra. Test 1 asks about differentiating attribute types, Armstrong's axioms for functional dependencies, proving the Armstrong's union rule and BCNF properties, and writing relational algebra queries over a database schema for university courseware. Test 2 asks about determining if a relation decomposition is lossless based on functional dependencies, writing relational algebra and tuple relational calculus queries over a supplier database schema, and writing SQL queries involving views and joins over customer, item, and order relations.
Download as DOC, PDF, TXT or read online from Scribd
Download as doc, pdf, or txt
You are on page 1/ 2
Test 1
a) Differentiate the following with the aid of an example.
i. Single Value and Multivalued attribute. [4] ii. Derived and Non-Derived Attribute. [4] iii. Candidate Key and Super Key. [4] b) List the Armstrong’s axioms for functional dependencies and explain what you understand by soundness and completeness of these axioms. [7] c) Prove the Armstrong’s union rule. [5] d) Prove that a relation with 2 attributes is in BCNF. [8] e) Let us assume a database for Web pages that keep data related to courses taught at a university. We consider the following part of the database schema:
The relation webpage stores data about web pages. Each web page is described by a unique identifier webID, a title webTitle, its url, and the number of hits to it. The relation courseware contains data about the course material of a course. Each courseware record stores a unique identifier cID to identify a courseware file, a description cDescription of the courseware file, a directory ftplocation from where the file can be downloaded, and the category of courseware (‘P’ = publication, ‘N’ = notes, ‘D’ = document, ‘E’ = executable). The relation ftpLink stores for each Web page the ftp links to multiple courseware files and for each courseware file the different Web pages to which it is linked.
Write relational algebra for the following queries
i. Find web ids and web titles of web pages which provide courseware with the cID of “COP5725”. ii. Find web ids and web titles of web pages which ONLY provide courseware with the cID of “COP 5725”. iii. Find the url of web pages which have the number of hits between [5,000, 10,000 ). iv. Find web ids and web titles of web pages which provide course materials with the cID of “COP5725” and the category of publication. [4X3] TEST 2 a) Given R (A, B, C, D, E) with the set of FDs, F {AB CD, A E, C D}. Is the decomposition of R into R1 (A,B,C), R2 (B,C,D) and R3(C,D,E) lossless? Prove. [10] b) Consider the following schema: Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real)
The key fields are underlined, and the domain of each field is listed after the field name. Therefore sid is the key for Suppliers, pid is the key for Parts, and sid and pid together form the key for Catalog. The Catalog relation lists the prices charged for parts by Suppliers. Write the following queries in relational algebra: i. Find the names of suppliers who supply some red part. [2] ii. Find the sids of suppliers who supply some red or green part. [2] iii. Find the sids of suppliers who supply some red part or are at 221 Packer Street. [2]
b) Write the above queries in tuple relational calculus. [6]
c) Consider the following relations with keys underlined Customer (C#, Cname, Address) Item (I#, Iname, Price, Weight) Order (O#, C#, I#, Quantity)
Write SQL queries for the following:
i. Create a view called “orders” that has the total cost of every order. [3] ii. List the names of customers who have ordered items weighing more than 1000. [3] iii. List the names of customers who have ordered at least one item priced over $500. [3]
