Answered You can hire a professional tutor to get the answer.

QUESTION

Branch Branch_ID: NUMBER Library Branch_Name: VARCHAR2(30) Library_ID: NUMBER Branch_Phone: VARCHAR2(30) Library_Name: VARCHAR2(30) Branch_Address:...

:

Identify all relationships and specify cardinality and business rules. For example: 1:M between Library and Branch: a library has many branches; a branch belongs to one and only one library.

For each entity, identify primary key and foreign key, if any. For foreign key, also specify parent entity and matching attribute in parent entity.

Write SQL DDL statements to create tables in Oracle and also implement primary key, foreign key, and NOT NULL constraint. Include all columns listed with the correct data type. This step is important, as in Question 2 you will run INSERT statements to populate the tables you created and then write SELECT statement to query those tables.

Write SQL statement for the following scenario:

Add a new customer Eric Short with Customer_ID 10 and Customer_Zip 23456.

Save changes permanently.

Change customer Eric Short zip from 23456 to 20231.

Cancel the change made in step c, restore data to its original status prior to step c.

Delete customer Eric Short.

Save changes permanently.

For SQL code, submit both source statements and results of running your statements.

BranchBranch_ID: NUMBERLibraryBranch_Name: VARCHAR2(30)Library_ID: NUMBERBranch_Phone: VARCHAR2(30)Library_Name: VARCHAR2(30)Branch_Address: VARCHAR2(30)Library_Phone: VARCHAR2(30)Library_ID: NUMBER (FK)Library_Address: VARCHAR2(30)Physical_ItemTransactionLibrary_CardPhysical_Item_ID: NUMBERTransaction_ID: NUMBERLibrary_Card_ID: NUMBERBranch_ID: NUMBER (FK)Catalog_Item_ID: NUMBER (FK)Date_checkout: DATELibrary_ID: NUMBER (FK)Copy_Number: NUMBERDate_Due: DATECustomer_ID: NUMBER (FK)Date_Purchased: DATEDate_Returned: DATECard_Number: VARCHAR2(30)Library_Card_ID: NUMBER (FK)PIN: VARCHAR2(8)Physical_Item_ID: NUMBER (FK)Date_Expire: DATECatalog_ItemCatalog_Item_ID: NUMBERCustomerTitle: VARCHAR2(30)Customer_ID: NUMBERDescription: VARCHAR2(30)Publisher: VARCHAR2(30)Customer_Firstname: VARCHAR2(30)Release_Date: DATECustomer_Lastname: VARCHAR2(30)Type: VARCHAR2(30)Customer_Street: VARCHAR2(30)Customer_City: VARCHAR2(30)Customer_State: VARCHAR2(20)BookDVDCustomer_Zip: VARCHAR2(10)Catalog_Item_ID: NUMBER (FK)Catalog_Item_ID: NUMBER (FK)ISBN: VARCHAR2(13)Length: VARCHAR2(30)Pages: NUMBER
Show more
LEARN MORE EFFECTIVELY AND GET BETTER GRADES!
Ask a Question