Write the sql code, Database Management System

Assignment Help:

Express the following queries in SQL:

(a) Write the SQL code that will create ALL the four tables given in the appendix. Your answer should not contain the primary key and foreign key constraint declaration. The data type for each attribute should be for the SQL Server 2005 convention.

(b) Having created the table structure in question (a) above, write SQL code to make the following change in the structure of the tables:

(i) Make the PubId the Primary Key in the Publisher table

(ii) Make the ISBN the primary key and the Pub_Id the foreign key in the Book table.

(iii) Make the AU_ID and the ISBN the Primary Key of the Book_Author table.

(iv) Make the AU_ID and the ISBN the foreign key of the Book_Author table.

(v) Make the AU_ID the Primary key of the Author table

(c) After creating the table structure above, you write the following SQL code to insert the first row in the Book table:

Insert into Book

Values(‘0-0038307-6-4', ‘dBASE III : A Practical Guide', 1985, 469);

When you run the above code SQL Server shows an error message. Explain the reason for the error. How will you correct the error? Explain your answer fully.

(d) If you wanted the au_id column to be automatically incremented when inserting new record in the author table, how would you modify the create statement in question(a) above so that it starts with the number 100 and increment by one every time you create a new record in that table.

(e) Write the SQL code to change the year born value from 1970 to 1971 for the author whose ID is 1.

(f) Write the SQL code required to list all book details whose title's name contains the word "Information".


Related Discussions:- Write the sql code

Data base design for a museum information system, In this practical assignm...

In this practical assignment, we will run through a complete database application development cycle, starting with the schema design, from an initial data import to database applic

Modelling and normalisation, Task Part A. Create an ERD from a business pro...

Task Part A. Create an ERD from a business problem. (50 marks) Read the following problem and complete the tasks listed below. THE ABS Blood Bank Australian Blood Services (ABS) i

The concurrent transactions, The Concurrent Transactions  Almost every ...

The Concurrent Transactions  Almost every commercial DBMS support multi-user environment. Therefore, allowing multiple transactions to proceed concurrently. The DBMS must make

What are the two main goals of parallelism, What are the two main goals of ...

What are the two main goals of parallelism? Load -balance multiple small accesses, so that the  throughput of such accesses enhances. Parallelize large accesses so that the res

Convert encrypted hsql database into csv format, Project Description: We...

Project Description: We have an encrypted HSQL Database which we want to change into csv format. HSQL DB . We have java application (jar file) which reads that database but n

What creates while a primary key is combined with a foreign, What it create...

What it creates while a primary key is combined with a foreign key? It creates Parent-Child relation ship between the tables that connect them while a primary key is combined w

Describe the storage structure of indexed sequential files, Describe the st...

Describe the storage structure of indexed sequential files and their access method.  Ans:   Storage structure of Indexed Sequential files and their access: To achieve fast rand

Differentiate between key and superkey, Differentiate between Key and super...

Differentiate between Key and superkey? Key and superkey - A key a single attribute or a combination of two or more attributes of an entity set in which is used to identify o

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd