------------------------------------------------------------------------
-- John David Ayers ----------------------------------------------------
-- September 14, 2009 --------------------------------------------------
-- Homework 1 ----------------------------------------------------------
------------------------------------------------------------------------

/*For this homework assignment, you will be crating a database named
MyLibrary and the tables in the database. For purposes of this
assignment only, you will assume that a book can have only one author.
*/

--1		10 points
--Write the SQL statement that will create a database named MyLibrary

CREATE DATABASE MyLibrary;

--2		10 points
--Write the SQL statement that indicates that all work from this point
--onward will be done in the MyLibrary database

Use MyLibrary;

--3		20 points
/*Write the SQL statement that will create a table named Authors
with the following columns:

Name				Type of data					Constraints
AuthorID			integer							primary key
AuthorLastName		no more than 50 characters		not null
AuthorFirstName		no more than 30 characters		not null
*/

CREATE TABLE Authors
(
	AuthorID			INT			PRIMARY KEY,
	AuthorLastName		VarChar(50) NOT NULL,
	AuthorFirstName		VarChar(20)	NOT NULL
);

--4		20 points
/*Write the SQL statement that will create a table named Books
with the following columns:

Name				Type of data					Constraints
BookID				integer							primary key
BookTitle			no more than 100 characters		not null
AuthorID			integer							foreign key to Authors
*/

CREATE TABLE Books
(
	BookID		INT				PRIMARY KEY,
	BookTitle	VarChar(100)	NOT NULL,
	AuthorID	INT	REFERENCES Authors(AuthorID)
);

--5		20 points
/*Write the SQL statement that will create a table named Patrons
with the following columns:

Name				Type of data					Constraints
PatronID			integer							primary key
PatronLastName		no more than 50 characters		not null
PatronFirstName		no more than 30 characters		not null
*/

CREATE TABLE Patrons
(
	PatronID			INT			PRIMARY KEY,
	PatronLastName		VarChar(50) NOT NULL,
	PatronFirstName		VarChar(30)	NOT NULL
);

--6		20 points
/*Write the SQL statement that will create a table named BooksCheckedOut
with the following columns:

Name				Type of data					Constraints
BookID				integer							foreign key to Books, not null
PatronID			integer							foreign key to Patrons, not null
DateCheckedOut		date							not null
DateReturned		date							may be null
*/

CREATE TABLE BookCheckedOut
(
	BookID			INT	REFERENCES Books(BookID) NOT NULL,
	PatronID		INT	REFERENCES Patrons(PatronID) NOT NULL,
	CONSTRAINT CheckedOut_pk PRIMARY KEY (BookID, PatronID),
	DateCheckedOut	DATETIME	NOT NULL,
	DateReturned	DATETIME	NULL
);
