Question

Practice Exercise: Create Logical Model, Relational Model, and Generate DDL code: This is a practice exercise for creating logical model, relational model, and generating schema DDL from relational model using Oracle Data Modeler Please create tables with your initial as suffix, e.g. instead of AP__ (replace AP with your initial) Data Dictionary: Table Name AP_EMP Column Name Datatype EMPNO NUMERIC EFNAME VARCHAR VARCHAR ELNAME JOB MGR VARCHAR NUMERIC DATE HIREDATE SAL COMM Table Name Column Name DEPTNO DNAME LOC Table Name Column Name NUMERIC NUMERIC AP_DEPT Datatype NUMERIC VARCHAR VARCHAR AP_PROJECT Datatype EMPLOYEE TABLE Size Optional/Mandatory Mandatory 4 30 Mandatory 30 Mandatory 30 Mandatory 4 Optional Mandatory (7,2) Mandatory (7,2) Optional DEPARTMENT TABLE Size Optional/Mandatory Mandatory 2 30 Mandatory 30 Mandatory ORGANIZATION PROJECTS Size Optional/Mandatory Comment Employee ID Number Employee Frist Name Employee Last Name Employee Functional Role Employee Manager ID Employee Join Date Employee Monthly Salary in USD Employee commission Comment Department ID Number Department Name Location City of the Department Comment PROJID PNAME STARTDATE ENDDATE Table Name Column Name GRADE HISAL LOSAL NUMERIC VARCHAR DATE DATE AP_SALGRADE Datatype VARCHAR NUMERIC NUMERIC 3 Mandatory 30 Mandatory Mandatory Mandatory SALARY GRADE TABLE Size Optional/Mandatory 2 Mandatory (7,2) Mandatory (7,2) Mandatory Highlighted in BLUE is Primary Key of the corresponding table. Project ID Number Name of the Project Start Date of the Project End Date of the Project Comment Salary Grade of Employee High Salary Value Low Salary Value STEPS FOR CREATING LOGICAL MODEL Create each Entity (table) with all Attributes (columns) with proper datatype, size, optional/mandatory, primary key, and comments for each attributes Create relationship among entities. If there is any Many to Many relationship, create associate (intersect) table in between with at least one attribute. With associate entity in middle, it will have two one to many relationships, with many side towards associate entity. For each relationship, select source key as primary key of the source side table (table which has one side of one- to-many relationship) Convert Logical model to Relational Model Rename Foreign Key column to same column name of primary key of referenced table. Expand Relational Model as necessary so that all details are clearly visible. General DDL script from the relational model and save it as PRACTICE_SCHEMA_DDL.sql on your laptop/desktop. LOGICAL MODEL: # EMPNO ELAME * EFNAME OMGR * SAL 0 COMM * HIREDATE * JOB * HOURS EMP EMP PROJECT PROJECT # PROJNO * PNAME * STARTDATE * ENDATE DEPTNO DNAME * LOC DEPT RELATIONAL MODEL P EMIPNO ENAME JOB MGR HIREDATE SAL COMM F DEPTNO EMP_PK (EMPNO) * HOURS PF EMPNO PF PROJID NUMBER (4) VARCHAR2 (30) VARCHAR2 (30) NUMBER (4) EMP_DEPT_FK (DEPTNO) EMP_EMP_FK (MGR) PROJID PNAME DATE NUMBER (7.2) NCLOB NUMBER (2) STARTDATE EMP NUMBER (4) NUMBER (4) NUMBER (3) PROEMP_PK (EMPNO, PROJID) PROEMP_EMP_FK (EMPNO) PROEMP_PROJECT_FK (PROJID) PROEMP PROJECT NUMBER (3) VARCHAR2 (30) DATE DATE * ENDDATE PROJECT_PK (PROJID) P DEPTNO DEPT NUMBER (2) VARCHAR2 (20) VARCHAR2 (20) * DNAME - LOC DEPT_PK (DEPTNO) SALGRADE GRADE HISAL LOSAL VARCHAR2 (2) NUMBER (7.2) NUMBER (7.2) SALGRADE_PK (GRADE)

Get Instant Homework HelpOn Your Mobile

Get Instant Homework HelpOn Your Mobile

Get Instant Homework HelpOn Your Mobile