CSE 444, Winter 2003SELECT DISTINCT title, nameSELECT title, name, yearSELECT title, nameSELECT year, count(*)SELECT pid, titleSELECT pid, title, year, nameCSE 444, Winter 2003Assignment #2: due Monday, Feb 10Objectives: To understand and be able to manipulate E/R diagrams. To be able to translatefrom E/R diagrams to a relational database. To understand functionaldependencies, BCNF and 3NF. To understand and be able to write queries overviews.Number of points: 100 points Questions: 1. [25 points] For the domain you are assigned in project Phase I, a. draw an E/R diagram for that domain, including your optional attributes.Indicate all key and cardinality constraints and any other assumptions thatyou make. b. Based on the E/R diagram, create your new relational schemas. You canrepresent the relational schema in TableName(attr1, attr2, … , attrN)instead of in XML format.2. [30 points][10 points] Write SQL statements to create tables that capture the E/R diagramabove. While creating the tables, consider the following requirements:a. [5 points] Choose realistic domains for the attributes.b. [5 points] Specify key (including non-primary key) and foreign keyconstraints according to the E/R diagram.c. [5 points] Define the constraint as below: For each ship, the period of asingle visit is less than 180 days.d. [5 points] Define the constraint as below: There are less ports on riversthan on seas.3. [15 points] Suppose you are given a relation R with four attributes, ABCD. Foreach of the following sets of functional dependencies, assuming those are the onlydependencies that hold for R, do the following: a. Compute {CD}+.b. Specify all of the candidate keys. c. Identify whether R is in BCNF or a 3NF or neither?d. If R is not in BCNF, decompose it into a collection of BCNF relations 1) BC, DA2) ABC, CA, CD3) ABCD, DA4) ABC, CAD4. [30 points] Consider the following relational schema, that includes two relations: Author(pid, name) Paper(pid, title, year) Citation(citingPid, citedPid)The Paper relation stores a set of published papers, including their publicationID, title and year of publication. The Author relation stores for every paper ID,the set of author names (so there is a row in the Author table for every author).The Citation relation stores for every paper ID, the set of other papers that cite it.For the following 4 query-view pairs, consider whether it is possible to use theview to answer the query. If yes, show the query that uses the view. If not, explainwhy.a. Query1.SELECT DISTINCT title, nameFROM Paper, Author, CitationWHERE year > 1995 AND Author.pid = Paper.pid AND Paper.pid = Citation.citedPidView1.SELECT DISTINCT Paper.pid AS pid, title, nameFROM Paper, AuthorWHERE year > 1995 AND Author.pid = Paper.pidb. Query2.SELECT title, name, yearFROM Paper, AuthorWHERE year > 1995 AND Author.pid = Paper.pidView2.SELECT title, nameFROM Paper, AuthorWHERE year > 1995 AND Author.pid = Paper.pid c. Query3.SELECT title, nameFROM Paper, AuthorWHERE year > 1997 AND Author.pid = Paper.pidView3.SELECT title, nameFROM Paper, AuthorWHERE year > 1995 AND Author.pid = Paper.pid d. Query4.SELECT year, count(*)FROM PaperGROUP BY yearView4.SELECT pid, year, count(*)FROM Paper, CitationWHERE Paper.pid = Citation.citedPidGROUP BY pid, yearFor each of the following views, consider whether it is updateable and specify thereason. e. View5.SELECT pid, titleFROM PaperWHERE year > 1995 f. View6.SELECT pid, title, year, nameFROM Paper, AuthorWHERE year > 1995 AND Author.pid =
View Full Document
Unlocking...