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;
The current scenario here is to check whether the highlighted code is right or wrong.
Create schema:
In a single exchange, create numerous tables, views, and grants within your personal schema using the CREATE SCHEMA statement. Each statement included in a CREATE SCHEMA statement is executed out by Oracle Database. The database finishes the transaction if each statement runs without failure. The database turns back all statements if any of them has an error.
A schema is not truly created by this statement. When a user is created, Oracle Database immediately produces a schema (see CREATE USER). This command enables you to add tables and perspectives to your schema and grant permissions to all those objects without having to execute multiple SQL statements in multiple exchanges.
The statements CREATE TABLE, CREATE VIEW, and GRANT can all be found in the CREATE SCHEMA statement. You must have the permissions needed to issue an included statements in order to execute a CREATE SCHEMA statement.
syntax for create schema:
CREATE SCHEMA AUTHORIZATION schema
{ create_table_statement
| create_view_statement
| grant_statement
}...
;
Syntax to create user:
create user username;
Step by step
Solved in 2 steps
