Develop the database in the attachment and resulting ERD using Oracle SQL Developer. Provided is a list of the key tables, columns, and constraints that you should focus on with the database. You may choose to add more tables and columns if you deem it necessary:

Customer

• CustomerID (PK).
• FirstName (NOT NULL).
• LastName (NOT NULL).
• Address.
• City.
• State.
• Zip.
• Country.
• Phone.
• Email (UNIQUE and NOT NULL).
• Username (UNIQUE and NOT NULL).
• Password (NOT NULL).

Order

• OrderID (PK).
• CustomerID (FK).
• OrderDate.
• ShipDate.
• OrderDetail.
• OrderDetailID (PK)
• OrderID (FK).
• ProductID (FK).
• Price.
• Quantity (CHECK > 0).

Product

• ProductID (PK).
• CategoryID (FK).
• ProductName.
• ProductDescription.
• UnitPrice.

Picture Category

• CategoryID (PK).
• CategoryName.
• Description.

To complete this assignment:

1. Create these tables in your database while planning the data types for each of these fields.
2. Generate the ERD for the tables using SQL Developer and take a screenshot of this to place into MS Word.
3. Provide the SQL Script that shows you have created a physical database containing appropriate data.

Make sure to specify only the tables created for the project in your database export. Provide your code and SQL scripts that you executed to create the forms and reports and show screenshots or other evidence that your scripts generated your reports successfully.

 

 

/*Connect to system*/
CONNECT <USER-NAME>/<PASSWORD>@<DATABASE_NAME>;
/*Create a script that sets up an ADMIN account in Oracle.*/
CREATE USER admin IDENTIFIED BY admin_password;
GRANT CONNECT, RESOURCE, DBA TO admin; --Set up roles
/*Set priviledges*/
GRANT CREATE SESSION, GRANT ANY PRIVILEGE TO admin;
GRANT UNLIMITED TABLESPACE TO admin;
GRANT SELECT,UPDATE,INSERT,DELETE ON schema.tableName TO admin;
/*Create a script that sets up an ORDINARY_USER account in Oracle.*/
CREATE USER normal_user IDENTIFIED BY user_password;
GRANT CONNECT TO normal_user;
/*Set up roles and priviledges*/
GRANT CREATE SESSION TO normal_user;
GRANT UNLIMITED TABLESPACE TO normal_user;
GRANT SELECT,UPDATE,INSERT,DELETE ON schema.tableName TO normal_user;
/*Load sample data*/
-- Type sqlplus on command line
-- enter username/password@databasename of admin
/*Create table users*/
CREATE TABLE USERS(
ID NUMBER(2) GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
USERNAME VARCHAR(30) NOT NULL,
PASSWORD VARCHAR(40) NOT NULL,
PRIMARY KEY(ID)
);
/*Insert sample data into it*/
INSERT INTO USERS (USERNAME, PASSWORD)
VALUES (Jane Goodall, Jane2020);
INSERT INTO USERS (USERNAME, PASSWORD)
VALUES (Janet Jackson, Jjack101);
INSERT INTO USERS (USERNAME, PASSWORD)
VALUES (Dana Smith, DSmith2001);
INSERT INTO USERS (USERNAME, PASSWORD)
VALUES (Mi Yong, MiY10123);
INSERT INTO USERS (USERNAME, PASSWORD)
VALUES (Indy MacLa, IMLa2101);
/* Validate data in the tableName database schema */
SQL Query DESC tableName;