For the conceptual schemata of the database, establish an

For the conceptual schemata of the database, establish an environment for the execution of simple and advanced queries in Oracle. The global schemata is as below EMP (ENUM, NAME, SAL, TAX, MGR, DNUM) DEPT (DNUM, NAME, MGR, AREA) SUPPLIER (SNUM, NAME, CITY) SUPPLY (PNUM, SNUM, DNUM, QTY) The logical relation structure of the tables described in the conceptual schema is as below; Relation Name: EMP Attribute Name Data Description ———————————————————— ENUM NUMBER (4) NAME VARCHAR(15) SAL NUMBER(9,2) TAX NUMBER( 8,2) DNUM NUMBER(3) MGR NUMBER(3) ———————————————————— Relation Name: DEPT Attribute Name Data Description ———————————————————— DNUM NUMBER(3) NAME VARCHAR(20) AREA CHAR(l) MGR NUMBER(3) ———————————————————— Relation Name: SUPPLIER Attribute Name Data Description ———————————————————— SNUM NUMBER(3) NAME VARCHAR(25) CITY CHAR(3) ———————————————————— Relation Name: SUPPLY Attribute Name Data Description ————————————————————- PNUM CHAR(4) SNUM NUMBER(3) DNUM NUMBER(3) QTY NUMBER(4) ———————————————————— Table Design Considerations: 

1. The entity integrity in EMP relation is maintained over the ENUM attribute which has the base value starting at 9201. 

2. It required that in EMP the column NAME, SAL, DNUM and MGR must always have an associated value. 

3. The EMP relation references DEPT to enforce the referential integrity on data existence. 

4. The entity integrity in DEPT relation is maintained over the DNUM attribute which has the base value starting at 101. 

5. The MGR attribute values in DEPT are unique. Where as the AREA attribute may take one of the values namely ‘N’ or ‘S’. 

6. The entity integrity in SUPPLIER relation is maintained over the SNUM attribute which can take values between 01and 30. 

7. The supplier names are unique. The CITY attribute values in SUPPLIER may take one of the values namely ‘NGP’ or ‘DLH’ or ‘UMR’. 

8. The entity integrity in SUPPLY relation is maintained over the combination of DNUM, SNUM and PNUM attributes. 

9. The SUPPLY relation references DEPT and SUPPLIER to enforce the referential integrity on data existence. 

Steps in Creation: 1. Use CREATE TABLE command to create the relations specified in the conceptual schemata, namely EMP, DEPT, SUPPLIER and SUPPLY. 

2. Create the SEQUENCE objects in Oracle for the various base values for ENUM, DNUM, SNUM. 

3. The relations to be created must conform to the schema structure specifications of the database. 

4. While creating the database relations using CREATE TABLE carefully analyze and enforce the domain constraints using the CHECK clause, NOT NULL clause or the UNIQUE clause. 

5. Enforce the entity integrity on the table by specifying the attribute (or a group of attributes) of interest as a PRIMARY KEY (Do not forget to specify proper constraint name like EMP _PK to the key constraint). 

6. Enforce the referential integrity on the table by specifying the attribute (or a group of attributes) of interest as a FOREIGNKEY (Do not forget to specify proper constraint name like EMP_FK_DEPT to the key constraint) that will reference a PRIMARY KEY column(s) in the referenced table(s). 

7. You can view the database table structure through DESCRIBE at the SQL prompt. 

8. Insert into each relational table the rows as per the table content specified (Insert up to 5 rows). 

9. After inserting the required data rows in the database, write SQL Code using SELECT statement to display the contents of each of the relation contained in the database. 10. Save your database for future query implementation using COMMIT command. 

Queries: 1. Write the SQL code that will display names of the employees along with their salary for the department with DNUM = 26. 

2. Write the SQL code that will list the names of the suppliers along with the department number and the department names, who supplies to the department ‘Personnel’. 

3. Write the SQL code that will display the name(s) of the supplier(s) who is/ are situated in the city of ‘UMR’ and who supplies parts in the north area of the company. 

4. Write the SQL code to list all employees whose name(s) starts with’ A’ along with the department which they belong and the area of posting. 

5. Write the SQL code that will list all those suppliers who supplies the part with PNUM = ‘KK78’ along with the quantity supplied and the department it was supplied to. 

6. Write the SQL code to create a relation EMP_ADMN that will contain the attributes ENUM, NAME, MGR and DNUM from the EMP relation. Also, create a relation EMP_PAY that contains the attributes ENUM, NAME, SAL, and TAX from the EMP relation.  These tables must contain the associated data in their parent tables. After creation of these tables save the database contents with COMMIT (Use: CREATE TABLE new_table AS SELECT col_1, col_2, … FROM table_1, table_2, …. WHERE ….. ;). 

7. Write the SQL code to display for each employee his gross earning (GRS_PAY) and the net earnings (NET_PAY) along with the employee name. (Use: SELECT col_1 AS XYZ, col_2 AS ABC, ……… FROM table_name WHERE condition 😉  Here GRS_PAY is an alias attributes that represents SAL whereas NET_PAY is calculated as SAL-TAX. 

8. Write the SQL code that will insert an employee record into EMP relation. The record contents are 9218, ‘Jyotika’, 890270.00, 89000.00, 988, 23  If the record cannot be inserted, write suitable SQL code to enable the insertion of this record in the EMP relation. (You may first be required to add the corresponding DEPT record, assume that this department is situated in Southern area and is named “Inventory & Purchase”)  After the successful insertion of the above employee record in EMP relation, save the current state of the database. 

9. Write the SQL code to display record of all employees who earn more than 800000.00 and are posted in the southern area of the company. 

10. Write the SQL code for the part with part number ‘PD33’ to find the department(s) of supply and the supplier’s name(s).