Group Project: Part 4—Database Creation

User Generated

lfhzzref47

Programming

Description

Modern software applications depend on a database management system (DBMS) as a foundational technology. For this reason, developers typically create the database as one of their first deliverables. In this Project, you work with your project group to create a functional database.

To prepare for this Project:

  • As a group, review the data requirements as expressed in your UML class diagram.
  • Review the “Oracle SQL Sample Commands Worksheet” document included in the week’s Resources.
  • Determine which DBMS product your group will use. Since all students are familiar with the Oracle DBMS from the previous class on database management systems, this is the recommended DBMS for this project. However, if the entire group is familiar with and chooses to use a different DBMS to complete the project, that is acceptable. Since all group members will be expected to build part of the database and retrieve information from it, all group members must agree to the use of a system other than Oracle. The course Instructor may or may not be familiar with systems other than Oracle.
  • Determine how group members will create the necessary tables, relationships, fields, and integrity constraints.
  • Each member of the group must be assigned at least two tables and one relationship to create from the group class diagram created in the Group Project Assignment from Week 3. When creating the tables and relationships, it is normally suggested that you create the tables in the proper order to avoid violating referential integrity (foreign keys). It is possible to first create the tables and then modify the tables to add the foreign keys. This second approach will be required when you are assigned tables to create which do not include the parent tables from the class diagram. Be sure to discuss this issue within your group. If you still have questions, ask your Instructor for clarification. To complete this Project, each group member will create a document that includes the following:
  • The names of the tables and relationships you were assigned- I was assigned the Product & Tax table
  • A listing of the SQL code to create the database structure of your assigned tables with appropriate integrity constraints. This listing must be executable on the selected DBMS product and include a screenshot showing that the code executed without error and that the tables were created. (See the “Oracle SQL Sample Commands Worksheet” document included in this week’s Learning Resources.)

Unformatted Attachment Preview

Oracle SQL Sample Commands Worksheet The following provides some pre-work to help you get started on the Oracle assignments. The following items are included in this document: • • • Information about the Oracle demonstration database included in Oracle Application Express A set of problems that require you to create and query tables in Oracle Express 11g Solutions to the problem set provided Take the time to execute at least some of the SQL commands presented below. This will give you a chance to get comfortable with working in the Oracle command execution environment. You may complete the work for this class with either the Application Express Editor or the Oracle Command Line editor. The Oracle Command Line Editor is not a full screen editor, you cannot copy and paste, and you cannot correct mistakes after you execute a command. The Application Express Editor is a full screen editor which most students prefer to use. If you have not used it before, there is a very short learning curve on Application Express. Most students find it worth the time. Oracle Express 11g comes with two demonstration databases already built. We will be working with the simpler of the two located in Application Express. In order to access the demonstration database you must have created a new user workspace in Application Express. (See Part 5: Application Express Editor usage information at the end of this document) BEFORE CONTINUING: Enter the Application Express environment and create your workspace as described in Part 5. Application Express Editor Usage information included at the end of this document. © 2015 Laureate Education, Inc. Page 1 of 9 Part 1. You may want to start by creating a UML Class Diagram of the database. This is optional, but is a good exercise and provides a nice visual representation of the Primary Key and Foreign Key relationships in the database. This diagram is very simple. The “describe” command is used to list attributes and data types for a specified table. describe emp Note the following tables are included in Oracle Application Express as part of the demonstration database. You do not need to create the tables, they already exist. DEPT DEPTNO DNAME LOC EMP EMPNO ENAME JOB MGR HIRE_DATE SAL COMM DEPTNO © 2015 Laureate Education, Inc. NOT NULL NUMBER(2) VARCHAR2(14) VARCHAR2(13) NOT NULL NUMBER(6) VARCHAR2(10) VARCHAR2(9) NUMBER($) DATE NUMBER(7,2) NUMBER (7,2) NUMBER(2) Page 2 of 9 Part 2. Use the Application Express Editor to execute the commands you create below. The Application Express Editor is a full screen editor. You can enter commands and change any and all lines as you move along. Enter the commands in the upper screen; the results are displayed in the lower screen. Blank lines on the input screen may create problems. For instructions on how to set up and use Application Express in Oracle 11g, see Part 5 of this document (“Application Express Editor Usage”). Execute each command in Oracle. Show the SQL commands to do the following: 1. Create a new table named JOBS that includes the following attributes and constraints: JOB_ID NOT NULL VARCHAR2(10) JOB_TITLE NOT NULL VARCHAR2(35) MIN_SALARY NUMBER(8,2) MAX_SALARY NUMBER(8,2)\ Additional Constraint: JOB_ID must start with the character “J”. 2. Create a new table named JOB_HISTORY that includes the following attributes and constraints: EMPNO START_DATE END_DAT JOB_ID DEPTNO NOT NULL NOT NULL NOT NULL NOT NULL NUMBER(6) DATE DATE VARCHAR2(10) NUMBER(2) The primary key for the JOB_HISTORY table is EMPNO + START_DATE. EMPNO and JOB_ID are all foreign keys linking JOB_HISTORY to the appropriate tables. 3. Create a new table named SALGRADE: GRADE LOSAL HISAL NUMBER(6) NUMBER(6) NUMBER(6) 4. Oops! We forgot to include the foreign key DEPTNO in the JOB_HISTORY table linking it to DEPT. Use the ALTER TABLE command to add this foreign key to JOB_HISTORY. 5. List all employees working in department 20. 6. List the name and job description of all employees hired after January 1, 1982. © 2015 Laureate Education, Inc. Page 3 of 9 7. List the name and employee id for all employees whose name starts with “A”, sort by last name. 8. List all employees with salaries between 6600 and 3000, sort the output by descending salary value. 9. Show the minimum and average salary for all employees 10. Show the deptno, minimum and average salary, and number of employees in each department grouped by deptno. 11. Show the same information as in #10 above, but only include employees with a salary above $1600. 12. Show the same information as in #10 above, but only include departments where the average salary is below $2,000 13. List the dname, ename, and hiredate for all employees sorted by dname within that by ename (must use join) 14. List the dname, ename and location for all employees in the accounting, sales, and operations departments (use the IN operator). Sort the output by ename. (must use join) 15. List the dname, ename, salary, and grade for employees ‘JONES’, ‘ALLEN’ and ‘JAMES’. (must use join) © 2015 Laureate Education, Inc. Page 4 of 9 Part 3. Solutions to problems: 1. CREATE TABLE jobs ( job_id VARCHAR2(10) CONSTRAINT check_job_id CHECK (job_id LIKE 'J%’), job_title VARCHAR2(35) NOT NULL, min_salary NUMBER(8,2) NOT NULL, max_salary NUMBER(8,2) NOT NULL, PRIMARY KEY (job_id) ); 2. CREATE TABLE job_history ( Empno Number(6) NOT NULL, Start_date DATE NOT NULL, End_date DATE NOT NULL, job_id VARCHAR2(10), Deptno NUMBER(2), PRIMARY KEY (Empno, start_date), FOREIGN KEY (Empno) REFERENCES emp, FOREIGN KEY (job_id) REFERENCES jobs, FOREIGN KEY (Deptno) REFERENCES depart ); 3. CREATE TABLE sal_grade ( Grade Losal Hisal Number(6), Number(6), Number(6)); 4. ALTER TABLE job_history ADD FOREIGN KEY (deptno) REFERENCES dept; 5. Select * from emp where deptno = 20; 6. Select ename, job from emp where hire_date > '01-JAN-82'; 7. Select ename, empno from emp where ename like 'A%' order by ename; 8. select * from emp where salary between 6600 and 3000 order by salary desc; 9. select min(salary), avg(salary) from emp; 10. select deptno, min(salary), avg(salary), count (*) from emp group by deptno; © 2015 Laureate Education, Inc. Page 5 of 9 11. select deptno, min(salary), avg(salary), count (*) from emp where salary > 1600 group by deptno; 12. select deptno, min(salary), avg(salary), count (*) from emp group by deptno having avg(salary) < 2000; Join commands below include both the old-style and new-style join solutions. 13. select dname, ename, hiredate from emp, dept where dept.deptno = emp.deptno order by dname, ename; select dname, ename, hiredate from emp natural join dept order by dname, ename; 14. select dname, employeet_name, location from emp, dept where dept.deptno = emp.deptno and dname IN ('ACCOUNTING', 'SALES', 'OPERATIONS') order by ename; select dname, ename, email from emp natural join dept where dname in ('ACCOUNTING', 'SALES', 'OPERATIONS') order by ename; 15. select dname, ename, salary, grade from emp, dept, salgrade where dept.deptno = emp.deptno and salary between losal and hisal and ename in (‘JONES’, ‘ALLEN’, ‘JAMES’); select dname, ename, salary, grade from emp natural join dept join salgrade on salary between losal and hisal where ename in (‘JONES’, ‘ALLEN’, ‘JAMES’); © 2015 Laureate Education, Inc. Page 6 of 9 Part 4. Optional work using the command line editor and the HR database. If you would like to also have practice working with the Oracle command line editor, you can access the HR database, the following command will log you in as user HR in the command line editor, allow you to create a password for HR, and provide a listing of tables in that database. alter user HR account unlock identified by yournewHRpassword; Select table_name from all_tables where tablespace_name = ‘USERS’ Next you must connect to Oracle as the user HR. At the SQL prompt(s) enter: Connect HR yournewHRpassword Enter the following command to list the HR tables. You may use the describe command to get a listing of the attributes for each table. Select table_name from user_tables; Describe Regions; etc, You may now access the HR tables as listed above. © 2015 Laureate Education, Inc. Page 7 of 9 Part 5. Application Express Editor Usage once Oracle Express 11g Installed Note: If you are using a newer or different version of Oracle, these instructions may need to be modified accordingly. Contact your Instructor for more information. • From Windows Start Menu: Oracle Express 11g Getting Started • Within browser window that opens : Select “Application Express” from the menu. • Login: Username: System Password: YourPasswordCreatedForSystem • Create a new user workspace: Database User Name: george (Just use your first name here) Application Express name: george Password: MyFavoritePassword Click on: Create Workspace • Click on: Already have an account? Login Here Enter credentials as requested: Password: MyFavoritePassword • Enter the SQL Workshop Select SQL Commands • In the top box, you may now enter SQL commands using a full-screen editor. The bottom box shows the results. • Enter a SQL command – Click “Run” to execute • Repeat until assignment completed • To view a History of commands so you can copy and paste, click the "History" tab above the lower box. To return to Application Express for another session in your newly created workspace: Repeat steps 1–3 above In the “Getting Started” box on the right select "Already have an account? Login Here". Enter your newly created Application Express credentials Click on SQL Workshop Click on SQL Commands Cut and paste all commands to Microsoft Word! © 2015 Laureate Education, Inc. Page 8 of 9 For additional information on the use of SQL Workshop click the "Help" tab while in the SQL Workshop application. © 2015 Laureate Education, Inc. Page 9 of 9 Expense Payroll Department Login UserName:Char Password: Varchar Expenseld: Int (PK) Vendorld: Int VendorName: String ExpenseDate: Datetime ExpenseAmount: Float Payrollld: Int(PK) Employeeld: Int(FK) EmployeeName: String EmployeeWorkedHours: Float PayrollDate: Datetime 1..1 EmployeegrossPay: Float EmployeePayRate: Float Employee GrossPaid: Float +access (in Employeeld: string, in password : String) String +verification ( in Employeeld: string, in password: String): String 1..1 1..1 +Create expense. +Changevendor(int Vendorrld), +EditExpenseAmount(Float ExpenseAmount), +Delete Expense 10.* Customer 1. * 1..1 +CreatePayrollo +AdjustHours Worked(Loat Hours Worked), +AdjustEmployeePayRate), +CreateDeduction(Float GrossPay), |+DeletePayrollo Invoice .1 1 * 1..1 Customerld: Int(pk) CustomerName: String CustomerAddress: String CustomerEmail: String CustomerPhoneNo: Int CustomerCreditCardinf: String Customer Shippinginf: String 0. 1.* Employee Report decription Payroll Rate Invoiceld: Int (PK) Customerld: Int (FK) CustomerName: String InvoiceAmount: Float Expense Date: Datetime Employeeld: Int (PK) EmployeeName: String Employee Address: Varchar EmployeeEmail: String EmployeeDOB: String EmployeePhoneNo: int EmployeePosition: Int +Registero +Logino +Update Reportid: Int (PK) Employeeld: Int (FK) Date: Datetime ReportType: String Employeeld: Int PayRateld: Int EmployeePayRate: Float Effective Date: Datetime +CreateInvoice: +EditinvoiceAmounto: String +Deletelnvoice(String): Boolean 1..1 1.. 1..* +GenerateReporto +PrintReporto +getEmployeeldo +getEmployeePayRate(): Float Order +UpdateEmployee Details +AddEmployee Details +Delete Employee Details +SearchEmployee 1..1 Payment 0..* Product Tax 1 * Orderld: Int (PK) OrderCustomerld: (FK) OrderDate: String OrderAddress: String OrderTotal: String OrderStatus: String OrderPrice: Double OrderCommision: String PaymentMethod: String PaymentReceived: Currency PaymentDate: Date 0.* Productid: Int(PK) ProductName: String ProductDescription: String ProductPrice: Currency + addPaymento: Boolean +editPaymento: Boolean +removePaymento: Boolean TaxID: Int (PK) Employeeld: int (FK) TaxName: String TaxType: String TaxRate: String Fita Rate: Int FicaRate: Int Salary +Create Ordero +EditOrder(IntOrderld) |+ Delete Ordero +addProducto +modifyProcto |+showProducto + deleteProducto +CalcTax 0 +getTaxRate 0:string 0.* 1..* 0.. Empld: Int (PK) EmpName: Varchar month: String date: Datetime SalaryRate: Flow Deductions: Int PayrollRate: Flow 0.. Vendor 0.* Supplier + getSalinfo (in Empld: int): String Sale Vendorld: Int(PK) VendorName: String VendorAddress: String VendorCity: String Vendor State: String VendorphN: String Productld: Int SaleDate: Datetime Sale Transaction: Int Supplierld: Int(PK) SupplierName: String 0.* SupplierPhNo: String SupplierAddress: String 1..* Yatavia Summers & Maria Orellana +ReceivePaymento + Supply Stock +TrackOrders +getVendorldo +getVendorName): String +getVendorphNo: String +getProductidt(int) +getSalesDate : Datetime +getSalesTransaction(Int)
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hello, for the two table that you provided that is Product & Tax table. I was able to do all that is required, the SQL,execution result and a screen-shot of the same.Nevertheless as you can see in the image extract of the UML there is no relationship betwee...


Anonymous
Great content here. Definitely a returning customer.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags