Take assessment: EXERCISE 1
Relational databasesdesign the folloads 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 I1. 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 ................................................................................ .. (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 ..
Part IIWrite 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 reliceal operations you buy, 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 IIIIn the file named part3.txt, provide answers to the following questions.1. in 1.2.2 RELATIONAL OPER ations, 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 Memno4. From Member, Book5. Where Memno = BorrowerMemno and6. Callnumber = 'qa76.9.d
26c66'7.) 8.
. UNION9 (.. SELECT MemNo10 FROM MEMBER, BOOK11 WHERE MemNo = BorrowerMemNo AND12 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. Answer: 2 ./ ****** Establish Database MANGEBOOK ****** / CREATE DATABASE MangeBOOKON (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'susertable ') = 1) Drop Table [dbo]. [book] go / ******* Object: Table [DBO]. [Publisher] Script date: 2 005-3-23 12:03:42 ****** / if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]) 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] [25) 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] Goalter Table [DBO]. [BOOK] with nocheck address [pk_book_1] Primary Key Clustered ([ISBN]) on [PRIMARY] Goalter Table [DBO]. [Publisher] with nocheck add constraint [pk_book] primary key clustered ([Publisherid]) on [primary] go / ****** Insert 5 lines to Table Book ****** / Insert Book (ISBN, Title, Primaryauthor, Edition, DateOfpublication, BookDescription, Price, Publisherid VALUES ('7-5053-7767-1', 'Data Structure and Algorithm Analysis ",' CLIFFORD A.SHAFFER ',' No. 1 ',' 2002 July ',' Using C Description Data Textbook ', '45.00', '56464' Insert Book (ISBN, Title, Primaryauthor, Edition, DateOfpublication, BookDescription , PRICE, Publisherid Values ('7-5614-0175-2', 'Discrete Mathematics Tutorial ",' Wu Zihua; Zhang Yi Li; Tang Changjie ',' 2nd Edition ',' September 1999 ',' Computer Professional Tutorial ', '27.50', '15546'
) Insert Book (ISBN, Title, Primaryauthor, Edition, Dateofpublication, BookDescription, Price, Publisherid VALUES ('7-04-011049-0', 'Database Operating System Concept', 'Abraham Silberschatz', '1st Edition ", 'May 2002', 'Database Tutorial English', '59.50 ',' 02523 ') Insert Book (ISBN, Title, Primaryauthor, Edition, Dateofpublication, BookDescription, Price, Publisherid) VALUES (' 7-81080-381- 6 ',' University English (2) ',' ',' No. 1 ',' 2002 Jan, 'University English Textbook', '34.80 ',' 15615 ') Insert Book (ISBN, Title, Primaryauthor , Edition, Dateofpublication, Price, Publisherid Values ('7-04-010545-4 ",' Linear Algebra ',' Zhou Yonglong, etc., '2nd Edition', '2002 February', University Engineering Mathematics', '16.00 ',' 02523 ') GO / ****** Insert 4 lines to table Publisher ****** / INSERT PUBLISHER (Name, Publisherid, Address) Values (' Higher Education Press ", '02523', 'Beijing Xicheng District 2') Insert Publisher (Name, Publisherid, Address) VALUES ('Sichuan University Press', '15546 ",' No. 24, South, Chengdu, South Insert Publisher (Name, Publisherid, Address) Values ('Electronics Press &' , '15615', 'Shanghai Foreign Mandarin University') GO / ****** Collect all the names and prices of all books published by Sichuan University Press and Higher Education Press ****** / SELECT TITLE, Pricefrom bo OKWHERE PublisherID IN (SELECT PublisherID FROM PUBLISHER WHERE PUBLISHER.Name = 'Sichuan University Press' OR Name = 'Higher Education Press') GO / ****** Write the SQL statement to retrieve the Publisher name of a particular book Title ****** / select namefrom publisherwhere Publisherid in (Select Publisherid from Book Where Book.title = 'Linear Algebra') GO3. If union is changed to Except in the queue, it means from the value of BorrowerMemno and CallNumber. The value of the value of 'qa76 ...' is selected in the two tables of 'Qa76 ...'.
