Please create the SQL DML statements necessary to implement the below database s

Question

Please create the SQL DML statements necessary to implement the below database schema as an OpenOffice database.

Please include all of the SQL statements and a screen shot that shows the structures that you implemented in OpenOffice or the database of your choice. The DML statements must accommodate the following elements:

Create statements to create tables from the entities defined as part of the Unit 4 assignment

Appropriate use of Null (and Not Null) parameters to ensure data validity

Appropriate use of constraint clauses to implement appropriate referential integrity

Use of data types and formats that is appropriate for the data in your database schema.

Appropriate use of keys including automatic generation of key values if appropriate

Please make appropriate use of Null (and Not Null) parameters to ensure data validity.

Please make appropriate use of constraint clauses to ensure the referential integrity of the relations in the schema.

Please make appropriate use of keys including the automatic generation of key values where appropriate.

Explanation / Answer

Answer)

SQL DML statements necessary to implement the below database schema:

CREATE TABLE Book (

ISBN_number integer not null primary key,

Title char(66),

author char(49),

cost decimal(10,7)

);

create table BookCopy(

ISBN_number integer not null,

sequence integer not null,

publicationdate date,

primary key(ISBN_number,sequence),

FOREIGN KEY (ISBN_number) REFERENCES Book(ISBN_number)

);

create table Borrower(

LibraryCard integer primary key not null,

name char(48),

address char(48),

postalcode char(28),

phonenumber char(28),

membershipdate date

);

create table BookLended(

LibraryCard integer not null,

checkoutdate date,

ISBN_number integer not null,

sequence integer not null,

returndate date,

librarianId integer,

FOREIGN KEY (ISBN_number) REFERENCES Book(ISBN_number),

FOREIGN KEY (LibraryCard) REFERENCES Librarian(LibraryCard),

FOREIGN KEY (sequence) REFERENCES BookCopy(sequence),

FOREIGN KEY (LibrarianId) REFERENCES Librarian(LibrarianId),

primary key(LibraryCard,checkoutdate,ISBN_number,sequence)

);

create table Librarian(

LibrarianId integer primary key not null,

name char(48),

phonenumber char(28),

Supervisor integer

);

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.