My first database job: the original title

Data | database
Take Assessment:exercise 1






Relational Databases
The design of the following two tables and turn in your submissions in the HTML file named.



Book:


Primary Author

Title

Isbn

PublisherID

Edition

Date of publication

Price

Book Description


PUBLISHER:


PublisherID

Name

Address


Part I



1. Identify the keys in your tables as follows. For this exercise, ignore the performance considerations while choosing the keys.



1. For each table, indicate the Primary Key (s) (PKs). If there is no PK. Fully justify your answer.



2. For each table, indicate the alternate Key (s) (AKs). If there is no AK. Fully justify your answer.



3. For each table, identify the Foreign Key (s) (FKs) and the integrity constraints. Fully justify your answer.



2. List all columns of your tables, and the domains (the data types and formats) of these columns. Fully justify your answer.



In a HTML table, list at least four rows and valid values in each of the tables. Turn in your submission in a file named bookpublisher.html.



Part II



Write SQL statements as specified below. You'll find this help on the setting up your DBMS useful. Submit your SQL statements in a file named Createdb.sql.



1. Write the DDL statements to create the two tables using the Create TABLE statement. Be sure to identify your primary keys and alternate keys in the statement.



2. Write the INSERT statements to inserts the four rows into each table.



3. Write the SQL statement to retrieve the title and price an all books published by either of two publishers (say "A Ddison Wesley "and" McGraw Hill "). In the file Rel-ops.txt, list which relational operations your used, from among the Select/project/join operations, in Orde R to perform this query. Explain the role of all operation in your query.



4. Write The SQL statement to retrieve the Publisher name of a particular book title (say "Fundamentals of Database S Ystems ").



Part III



In the file named Part3.txt, provide answers to the following questions.



1. In 1.2.2 Relational Operations, there is an example of the "set difference operation:the result of r-s is Prov IDed to you. Provide the result of the set difference Operation S-r.



2. In the class notes, the following UNION example is discussed. Give a prosaic description of the results that would be retrieved by replacing
UNION
With EXCEPT in the following query:



3. (SELECT Memno



4. From member, book



5. WHERE Memno = Borrowermemno and



6. Callnumber = ' qa76.9.d26c66 '



7.)



8.
UNION




9. (SELECT Memno



From the book



WHERE Memno = Borrowermemno and



Callnumber = ' Qa76.9.d7e53 '



13.);



Your submission for this exercise should consist of the following files:BookPublisher.html and Createdb.sql, Rel-ops.txt, and Part3.txt.



To help yourself does your best on this assessment, consult this general list of grading guidelines.