我的第一次数据库作业:题目原文

答案：
Take Assessment: Exercise 1




 

Relational Databases
Design the following two tables and turn in your submissions in an HTML file named BookPublisher.html. 



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



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



2.      For each table, indicate the Alternate Key(s) (AKs). If there is no AK, state so. 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 an HTML table, list at least four rows with 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 may find this help on 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 insert the four rows into each table. 



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



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



Part III



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



1.      In 1.2.2 Relational Operations, there was an example of the set difference operation: the result of r - s was provided to you. Provide the result of the set difference operation s - r. 



2.      In the class notes, the following UNION example was discussed. Give a prosaic description of the results that will 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



10.                     FROM     MEMBER, BOOK



11.                     WHERE    MemNo = BorrowerMemNo AND



12.                     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 do your best on this assessment, consult this general list of grading guidelines.




 



 

上一个：sql的三个主要语句及一点技巧
下一个：发布MySQL集群自动安装脚本1.0！