Database Management Assignment - SQL Queries for Pine Valley Furniture Corporation
Part 1 - Define and Populate the Database
Use Oracle Database Configuration Assistant to create a database named PVFC-{Yourlastname} Make sure that it is NOT a container database. Also provide passwords for users SYS and SYSDBA.
Connect as user sys with role as sysdba. In DBA view create a user, grant user appropriate privileges to connect and manipulate database objects. Connect to the database as this user.
Use the attached DDL script CreateBigPV.sql to create tables and define constrains. Please check the script to make use that you understand different data types, primary key, and foreign key constraints.
Create the relational model for the database. Compare it with Figure 2.22 in the textbook. The following table gives the correspondence between the figure and tables. You may notice in translating E-R diagrams to tables that some of the entities have been renamed - e.g. HAS SKILL in figure has been implemented as EmpoyeeSkill_T.
Now use scripts Load1BigPV.sql and Load2BigPV.sql in that order to load data into the database. (The order is important because of the referential integrity constraints; we have to load data into tables on 1 side of a relationship before we can load matching records on the many side of the relationship.)
Part 2 - SQL Queries for Data Retrieval
Create a New Project
Switch to data perspective and create a new project based on the connection. Examine data in each table using SELECT command.
Practice SQL
Use Oracle SQL Developer to practice building SQL queries for exercises 6-57 through 6-85 on pages 284-286. Some the queries will be very simple (e.g. 6-58, 6-59), some will require running aggregates (e.g. 6-69), some will require you to find additional date function in Oracle (e.g., 6-77). More you practice, better you will become at developing SQL statements,
Use definition on page 309 to create view TSALE. Use this view to answer Query 1 on page 309. Change SELECT query on page 310 to create view TOPTERRITORY query and then use this view to answer Query 2 on page 310.
Textbook - Modern Database Management, 12th edition by Jeffrey A. Hoffer, V. Ramesh and Heikki Topi.