我的第一次数据库作业:题目原文及部分答案

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.D 26C66' 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. 答案: 2. /****** 建立数据库MangeBOOK ******/ CREATE DATABASE MangeBOOK ON (NAME = MangeBOOK,  FILENAME = 'MangeBOOK.mdf',  SIZE = 10,  MAXSIZE = 60,  FILEGROWTH = 4) LOG ON (NAME = 'MangeBOOK_Log',  FILENAME = 'MangeBOOK_Log.ldf',  SIZE = 5MB,  MAXSIZE = 30MB,  FILEGROWTH = 4MB) GO /****** Object:  Table [dbo].[BOOK]    Script Date: 2005-3-23 12:03:42 ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BOOK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[BOOK] GO /****** Object:  Table [dbo].[PUBLISHER]    Script Date: 2005-3-23 12:03:42 ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PUBLISHER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[PUBLISHER] GO /****** Object:  Table [dbo].[BOOK]    Script Date: 2005-3-23 12:03:43 ******/ CREATE TABLE [dbo].[BOOK] (  [ISBN] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,  [Title] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,  [PrimaryAuthor] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,  [Edition] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,  [DateofPublication] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,  [BookDescription] [char] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,  [Price] [money] NOT NULL ,  [PublisherID] [char] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY] GO /****** Object:  Table [dbo].[PUBLISHER]    Script Date: 2005-3-23 12:03:44 ******/ CREATE TABLE [dbo].[PUBLISHER] (  [Name] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,  [PublisherID] [char] (5) COLLATE Chinese_PRC_CI_AS NOT NULL ,  [Address] [char] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[BOOK] WITH NOCHECK ADD  CONSTRAINT [PK_BOOK_1] PRIMARY KEY  CLUSTERED  (   [ISBN]  )  ON [PRIMARY] GO ALTER TABLE [dbo].[PUBLISHER] WITH NOCHECK ADD  CONSTRAINT [PK_BOOK] PRIMARY KEY  CLUSTERED  (   [PublisherID]  )  ON [PRIMARY] GO /****** 插入5行到表BOOK ******/ INSERT BOOK (ISBN,Title,PrimaryAuthor,Edition,DateofPublication,BookDescription,Price,PublisherID) VALUES ('7-5053-7767-1','数据结构与算法分析','Clifford A.Shaffer','第1版','2002年7月','采用C++描述的数据结构教材','45.00','56464') INSERT BOOK (ISBN,Title,PrimaryAuthor,Edition,DateofPublication,BookDescription,Price,PublisherID) VALUES ('7-5614-0175-2','离散数学教程','吴子华;张一立;唐常杰','第2版','1999年9月','计算机类专业用教程','27.50','15546') INSERT BOOK (ISBN,Title,PrimaryAuthor,Edition,DateofPublication,BookDescription,Price,PublisherID) VALUES ('7-04-011049-0','数据库操作系统概念','Abraham Silberschatz','第1版','2002年5月','数据库教程英文版','59.50','02523') INSERT BOOK (ISBN,Title,PrimaryAuthor,Edition,DateofPublication,BookDescription,Price,PublisherID) VALUES ('7-81080-381-6','大学英语(2)','略','第1版','2002年1月','大学英语教材','34.80','15615') INSERT BOOK (ISBN,Title,PrimaryAuthor,Edition,DateofPublication,BookDescription,Price,PublisherID) VALUES ('7-04-010545-4','线性代数','周厚隆等','第2版','2002年2月','大学工科数学','16.00','02523') GO /****** 插入4行到表PUBLISHER ******/ INSERT PUBLISHER (Name,PublisherID,Address) VALUES ('高等教育出版社','02523','北京市西城区德外大街4号') INSERT PUBLISHER (Name,PublisherID,Address) VALUES ('四川大学出版社','15546','成都市一环路南一段24号') INSERT PUBLISHER (Name,PublisherID,Address) VALUES ('电子工业出版社','56464','北京市海淀区万寿路173邮箱') INSERT PUBLISHER (Name,PublisherID,Address) VALUES ('上海外语教育出版','15615','上海外国语大学内') GO /****** 收集所有由四川大学出版社和高等教育出版社出版的所有书籍的名称和价格 ******/ SELECT Title, Price FROM BOOK WHERE PublisherID IN (SELECT PublisherID FROM PUBLISHER WHERE PUBLISHER.Name = '四川大学出版社' OR Name = '高等教育出版社') GO /****** Write the SQL statement to retrieve the Publisher name of a particular book title ******/ SELECT Name FROM PUBLISHER WHERE PublisherID IN (SELECT PublisherID FROM BOOK WHERE BOOK.title = '线性代数') GO 3. 如果在队列中把UNION换成EXCEPT，那就意味着从值为BorrowerMemNo 和CallNumber的值为'QA76...'的两个表中选择MemNo。 然后用同样的办法选择CallNumber 是'QA76.9D7E53'的另一个表，它的值和先前表的值不同。所以队列重有一个错误。 如果我的答案有误请各位指出,谢谢!