Can someone look at schema code and and tell me where I may have went wrong? Someone ACTUALLY look at my code and fix it so i can run it succesfully. Look at bold code and tell me if its right or not.
Can someone look at schema code and and tell me where I may have went wrong? Someone ACTUALLY look at my code and fix it so i can run it succesfully. Look at bold code and tell me if its right or not.
CREATE SCHEMA library AUTHORIZATION ADEEL;
create user gerstld;
CREATE TABLE library.BRANCH (
ID int CONSTRAINT BRANCH_ID NOT NULL
, Name varChar(90) CONSTRAINT BRANCH_Name NOT NULL
, Address varChar(200) CONSTRAINT BRANCH_Address NOT NULL
, CONSTRAINT BRANCH_pk PRIMARY KEY (ID)
);
CREATE TABLE library.BOOKS (
Card_Catalog varChar(7) CONSTRAINT BOOKS_Card_Catalog NOT NULL
, Author varChar(90) CONSTRAINT BOOKS_Author NOT NULL
, Title varChar(20) CONSTRAINT BOOKS_Title NOT NULL
, Edition int CONSTRAINT BOOK_Edition NOT NULL
, ISBN int CONSTRAINT BOOK_ISBN NOT NULL
, Copy_Number int CONSTRAINT BOOK_Copy_Number NOT NULL
, Replacement_Costs_Cents money CONSTRAINT BOOK_Replacement_Costs_Cents NOT NULL
, Publication_Year date CONSTRAINT BOOKS_Publication_Year NOT NULL
, Branch_id int CONSTRAINT BOOKS_Branch_id NOT NULL
, CONSTRAINT BOOKS_pk PRIMARY KEY (ISBN)
, CONSTRAINT BOOKS_fk1 FOREIGN KEY (Branch_ID) references library.Branch (ID)
);
CREATE TABLE library.PUBLISHER (
Publisher_Name varChar(100) CONSTRAINT PUBLISHER_Publisher_Name NOT NULL
, Publisher_City varChar(90) CONSTRAINT PUBLISHER_Publisher_City NOT NULL
, Publisher_Address varChar(200) CONSTRAINT PUBLISHER_Publisher_Address NOT NULL
, CONSTRAINT publisher_pk PRIMARY key (Publisher_Name)
);
CREATE TABLE library.BORROWER (
Borrower_ID int CONSTRAINT BORROWER_Borrower_ID NOT NULL
, First_Name varChar(40) CONSTRAINT BORROWER_First_Name NOT NULL
, Last_Name varChar(50) CONSTRAINT BORROWER_Last_Name NOT NULL
, Borrower_Address varChar(200) CONSTRAINT BORROWER_Borrower_Address NOT NULL
, Branch_ID int CONSTRAINT BORROWER_Branch_ID NOT NULL
, CONSTRAINT BORROWER_pk PRIMARY KEY (Borrower_ID)
, CONSTRAINT BORROWER_fk1 FOREIGN KEY (Branch_ID) references library.Branch (ID)
);
CREATE TABLE library.CAN_BORROW (
ISBN int CONSTRAINT BOOKS_ISBN NOT NULL
, Borrower_ID int CONSTRAINT BORROWER_Borrower_ID NOT NULL
, borrow_date date CONSTRAINT CAN_BORROW_borrow_date NOT NULL
, due_date date CONSTRAINT CAN_BORROW_due_date NOT NULL
, return_date date CONSTRAINT CAN_BORROW_return_date NOT NULL
, CONSTRAINT can_borrow_pk PRIMARY KEY (ISBN, Borrower_ID)
, CONSTRAINT can_borrow_fk1 foreign key (ISBN) references library.books (ISBN)
, CONSTRAINT can_borrow_fk2 foreign key (Borrower_ID) references library.borrower (Borrower_ID)
);
CREATE TABLE library.PUBLISHED_A (
ISBN int CONSTRAINT PUBLISHED_A_ISBN NOT NULL
, Publisher_Address varChar(200) CONSTRAINT PUBLISHED_A_Publisher_Address NOT NULL
, CONSTRAINT PUBLISHED_pk PRIMARY KEY (ISBN, Publisher_Name)
, CONSTRAINT PUBLISHED_fk1 FOREIGN KEY(ISBN) REFERENCES library.BOOKS(ISBN)
, CONSTRAINT PUBLISHED_fk2 FOREIGN KEY(Publisher_Address) REFERENCES library.Publisher (Publisher_Address)
);
CREATE TABLE library.RESERVATION (
Branch_ID int CONSTRAINT RESERVATION_Branch_ID NOT NULL,
Reserve_Date date CONSTRAINT RESERVATION_Reserve_Date NOT NULLl,
Borrower_ID int CONSTRAINT RESERVATION_Borrower_ID NOT NULL,
ISBN int CONSTRAINT RESERVATION_ISBN NOT NULLl,
CONSTRAINT RESERVATION_pk1 PRIMARY KEY (Branch_ID)
CONSTRAINT RESERVATION_pk2 PRIMARY KEY (Branch_ID, Borrower_ID, ISBN)
CONSTRAINT RESERVATION_fk1 FOREIGN KEY (Borrower_ID) REFERENCES library.Borrower (Borrower_ID)
CONSTRAINT RESERVATION_fk2 FOREIGN KEY (ISBN) REFERENCES library.Books (ISBN)
);
grant SELECT on schema::library to gerstld;
GO;
Step by step
Solved in 2 steps
