Search results for 'My Guitar Shop'

Build a small, simple database for your personal use. Some suggestions might be an address book, tax deduction tracking, or automotive history. You must use a relational database management system (RDBMS). You may use any RDBMS you wish (e.g. Access, SQL Server, Oracle, DB2, MySQL, etc.), although if you’re unfamiliar with any product, then you will probably find Access to be the easiest to use. Your database must have at least 2 related tables. All of your tables should be fully normalized, such that redundant data is removed. You must show how your tables were created (i.e. Design Views or SQL). You must include all applicable integrity constraints (i.e. primary keys, foreign keys, required/NOT NULL, unique, and validation rules/CHECK constraints). Submit for grading a single Word or .pdf file with screen snapshots showing all steps in the creation of your database, the creation of your tables, the population of your tables with at least 3 rows of data each, and at least 3 queries of your data. Do not submit your actual database. Include at the end of your Word or .pdf file a 200- to 350-word write-up to respond to the following: • As you built your database, what challenges did you encounter? What tips do you have for your fellow students? Learn More

GENERAL OVERVIEW
Scenario/Summary
My colleague, Ann Henry, operates a regional training center for a commercial software organization. She created a database to track client progress so she can analyze effectiveness of the certification program. CLIENT, COURSE, and COURSE_ACTIVITY are three of the tables in her database. The CLIENT table contains client name, company, client number, pre-test score, certification program and email address. The COURSE_ACTIVITY table contains client number, course code, grade, and instructor notes. The COURSE table contains the course code, course name, instructor, course date, and location. Although she and her instructors enter much of the data themselves, some of the data are extracted from the corporate database and loaded into her tables.

Loading the initial data was easy. For grade entry at the end of each course, a former employee created a data entry form for the instructors. Updating most client information and generating statistics on client progress is not easy because Ann does not know much SQL. For now, she exports the three tables into three spreadsheets. To look up a grade in the COURSE_ACTIVITY spreadsheet, she first has to look up client number in the CLIENT spreadsheet. While this is doable, it is certainly not practical. For statistics, she sorts the data in the COURSE_ACTIVITY spreadsheet using multiple methods to get the numbers she needs.

Every month, Ann's database tables need to be refreshed to reflect changes in the corporate database. Ann describes this unpleasant task. She manually compares the contents of newly extracted data from corporate to the data in her spreadsheets, copies in the new values, and then replaces the database contents with the new values.

Ann needs our help. Let’s analyze her situation and determine what advanced SQL she could use to make her tasks easier.
     
L A B O V E R V I E W

Scenario/Summary

The purpose of this lab is to explore join operators to determine which, if any, are appropriate for solving Ann's business problems, as described in this week's lecture.

Since Ann prefers to work from Excel spreadsheets, she wants her CLIENT and COURSE_ACTIVITY tables exported into one spreadsheet rather than two, as she is currently using. We need to determine which, if any, of the join operators will provide the data she wants for the single spreadsheet. (Note: we will not perform the export, just determine how to retrieve the necessary data.) Using the spreadsheet, she will be able to determine:

1. Which course(s) a specific client has taken
2. What grade(s) a specific client has earned in a specific course
3. Which clients did not take any courses
4. Which courses were not taken by any client

Here are results from DESCRIBE commands that show structure (columns and their data types) of tables CLIENT and COURSE_ACTIVITY. You may refer to it while constructing your queries.

SQL*Plus: Release 10.2.0.1.0 — Production on Thu Jun 14 22:38:52 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 — 64bit Production
With the Partitioning, OLAP and Data Mining Options

SQL> desc course_activity

SQL>

For this lab you will be creating several documents. First, write your queries in Notepad to create a script file that will contain all of the queries asked for in lab steps 4 through 13. You can (and should) test each query as you write it to make sure that it works and is returning the correct data. Once you have all of your queries written then create a SPOOL session and run your entire script file. Be sure that you execute a SET ECHO ON session command before running the file so that both the query and the output will be captured in the SPOOL file. IMPORTANT: If you are using Windows Vista you will need to create a directory on your C: drive to SPOOL your file into. Vista will not allow you to write a file directly to the C: drive. This will give you two files for the lab. The third file will the be the Lab1 Report document found in Doc Sharing. You will need to put your responses to the questions asked in the various lab steps.

Now let's begin.

L A B S T E P S

STEP 1: Start Oracle SQL*Plus via Citrix

Log into the Citrix iLab environment. Open your Oracle folder, select SQL Plus and log in to your database instance. Use "sys" as User Name, and "oracle" as the Password. Enter the Host String as "DB9999.world as sysdba" where 9999 is the database number you have been assigned.

STEP 2: Initialize tables

Download the pupbld.sql and Lab1_init.sql files associated with the links to your C: drive or to the F: drive in your Citrix environment. You will need to open each of the files and edit the connection string to reflect your instance name. The pupbld.sql file has two connections strings; one at the top of the script and another at the bottom. Be sure to change both of these to reflect your instance name.

Once you have done this then run the pupbld.sql script first (DO NOT copy and paste it) in your SQL*Plus session. The script will create the product_user_profile synonym in the SYSTEM account which will be used each time you log in as a normal user.

Next run the lab1_init.sql script in your session. The script will create a new user (DBM449_USER) that will be used in various labs in this course. You can find the password for this new user by looking at the CREATE USER statement in the script file. Disregard the DROP TABLE error messages. They occur because the script is designed to work regardless of whether you have already created the tables or not. This way, you may run it if you ever decide to resent the contents of your tables to the original values. When you run the script for the first time, the error messages appear as you attempt to drop tables that do not exist.

Once the script has finished you will be logged into the new user and ready to start your lab.

STEP 3: Verify your tables

You want to verify that everything completed successfully. To do this execute a SELECT * FROM TAB statement to make sure all 5 tables were created and then you can execute a SELECT COUNT(*) FROM statement using each of the table names. You should find the following numbers of records for each table.

• CLIENT table - 5 rows
• COURSE table - 5 rows
• COURSE_ACTIVITY table - 6 rows
• CORP_EXTRACT1 table - 3 rows
• CORP_EXTRACT2 table - 0 rows

NOTE: In the following steps when writing your queries be sure to list the tables in the FROM clause in the same order they are listed in the instructions. Reversing the order of the tables in the FROM clause will produce an incorrect results set

STEP 4: Using the FULL OUTER JOIN operator

Join the CLIENT and COURSE_ACTIVITY tables using a FULL OUTER JOIN.

• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.

Will the FULL OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.

STEP 5: Using the RIGHT OUTER JOIN operator

Join the CLIENT and COURSE_ACTIVITY tables using a RIGHT OUTER JOIN.

• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.

Will the RIGHT OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.

STEP 6: Using the LEFT OUTER JOIN operator

Join the CLIENT and COURSE_ACTIVITY tables using a LEFT OUTER JOIN.

• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.

Will the LEFT OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.

STEP 7: Using the NATURAL JOIN operator

Join the CLIENT and COURSE_ACTIVITY tables using a NATURAL JOIN.

• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
• Will the NATURAL JOIN be helpful to Ann? Place your response in the lab report document for this step.

STEP 8: Using the INNER JOIN operator

Join the CLIENT and COURSE_ACTIVITY tables using a INNER JOIN.
Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.

Will the INNER JOIN be helpful to Ann? Place your response in the lab report document for this step.

Write a conclusion based on the five steps above, which join - if any - should Ann use to populate the spreadsheet that can answer her questions.

STEP 9: Using the UNION operator 

Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the UNION operator.

• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.

Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.

STEP 10: Using the UNION ALL operator

Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the UNION ALL operator.

• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.

Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.

STEP 11: Using the INTERSECT operator

Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the INTERSECT operator.

• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.

Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.

STEP 12: Using the MINUS operator

Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the MINUS operator.

• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.

Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.

STEP 13: Using subqueries

Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using a subquery with NOT IN operator.

• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.

Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.

Deliverables
    
What is Due

Submit your spooled lab file with the queries and results sets along with the completed Lab 1 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.

Learn More

Types of Relationships and Sample Databases

Part 2
Come up with an original example of a one to many unary relationship. Create an example model of this relationship using the MySQL Workbench data modeler.

Part 3
Come up with an original example of a ternary relationship. Create an example model of this relationship using the MySQL Workbench data modeler.

Part 4
Using MySQL Workbench – create a data model for on Adult Baseball league which supports all of the following data requirements:
Teams
    A team has a unique number associated with it
    Team Name
    Team City
    Each team has more than one player associated with it

Players
    Each Player has a unique number
    A player may play or have played for more than one team
    The number of years and batting average is tracked for each team the player played for.

Coaches
    In this league, all coaches are also players.
    A Team can have more than one coach.

Bats
    A team can have multiple bats
    Each bat is identified by a unique serial number.

For each of the three models above, you should export your model as a single page PDF and submit the resulting files.

Learn More

CS371 Database Design Week 5 Assignment Chapter 6 Questions

Chapter 6 Exercises 1 page 154: Use MySQL Workbench to construct the relational diagram for this database.
Chapter 6 Exercises 2 page 154: Use MySQL Workbench to construct the relational diagram for this database.
Chapter 6 Exercise 5 page 155: Submit responses in Word Doc format.

You may submit a single word doc with SQL queries and your models pasted in – OR – submit separate files including PDFs of your models and a word doc with your queries.

Chapter 6 Exercise 1 page 154: Use MySQL Workbench to construct the relational diagram for this database:

1.  Leslie’s Auto Sales has a relational database with which it maintains data on its salespersons, its customers, and the automobiles it  sells. Each of these three entity types has a unique attribute identifier.
The attributes that it stores are as follows:
• Salesperson Number (unique), Salesperson Name, Salesperson Telephone, Years with Company
• Customer Number (unique), Customer Name, Customer Address, Value of Last Purchase From Us
• Vehicle Identification Number (unique), Manufacturer, Model, Year, Sticker Price Leslie’s also wants to keep track of which salesperson sold
which car to which customer, including the date of the sale and the negotiated price. Construct a relational database for Leslie’s Auto Sales.

Answer:


Chapter 6 Exercise 2 page 154: Use MySQL Workbench to construct the relational diagram for this database.

2.  The State of New York certifies firefighters throughout the state and must keep track of all of them, as well as of the state’s fire departments. Each fire department has a unique department number, a name that also identifies its locale (city, county, etc.), the year it was established, and its main telephone number. Each certified firefighter has a unique firefighter number, a name, year of certification, home
telephone number, and a rank (firefighter, fire lieutenant, fire captain, etc.) The state wants to record the fire department for which each firefighter currently works and each firefighter’s supervisor. Supervisors are always higher-ranking certified firefighters.
Construct a relational database for New York’s fire departments and firefighters.

Answer:


Chapter 6 Exercise 5 page 155: Submit responses in Word Doc format.

5. In the General Hardware Corp. database of Figure 6.1, what would happen if:
a. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is restrict and an attempt is made to delete the record for customer 2198 in the CUSTOMER relation?
b. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is cascade and an attempt is made to delete the record for customer 2198 in the CUSTOMER relation?
c. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is set to null and an attempt is made to delete the record for customer 2198 in the CUSTOMER relation?
d. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is restrict and an attempt is made to delete the record for employee 33779 of customer 2198 in the CUSTOMER EMPLOYEE relation?
e. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is cascade and an attempt is made to delete the record for employee 33779 of customer 2198 in the CUSTOMER EMPLOYEE relation?
f. The delete rule between the CUSTOMER and CUSTOMER EMPLOYEE relations is set-to-null and an attempt is made to delete the record for employee 33779 of customer 2198 in the CUSTOMER EMPLOYEE relation?

Answer:

Learn More

GU CS371 Database Design Week 4 Assignment Chapter 5 Relational Database Model

The assignment comes from Chapter 5 Minicase 1 but includes an additional step:
Using MySQL Workbench, create the relational data model for the database for happy cruise lines, which includes 6 tables. (Note: Be sure primary and foreign keys are correct). Export the PDF version of your model for submission.
Then complete steps a – g. Note: On step g, provide correct SQL syntax for finding the result to each item i – viii. i.e. Do not use the informal relational approach – looking for valid, correct SQL as described in Chapter 4.
You may submit a single word doc with SQL queries and your model pasted in – OR – submit 2 separate files including a PDF of your model and a word doc with your queries.

Fundamentals of Database Management Systems 2nd Edition Chapter 5 Minicase 1
1. Consider the following relational database for Happy Cruise Lines. It keeps track of ships, cruises, ports, and passengers. A “cruise” is a particular sailing of a ship on a particular date. For example, the seven-day journey of the ship Pride of Tampa that leaves on June 13, 2009, is a cruise. Note the following facts about this environment.
Both ship number and ship name are unique in the SHIP Relation.
A ship goes on many cruises over time. A cruise is associated with a single ship.
A port is identified by the combination of port name and country.
As indicated by the VISIT Relation, a cruise includes visits to several ports, and a port is typically included in several cruises.
Both Passenger Number and Social Security Number are unique in the PASSENGER Relation. A particular person has a single Passenger Number that is used for all of the cruises that she takes.
The VOYAGE Relation indicates that a person can take many cruises and a cruise, of course, has many passengers.

SHIP Relation
Ship Number
Ship Name
Ship Builder
Launch Date
GrossWeight

CRUISE Relation
Cruise Number
Start Date
End Date
Cruise Director
Ship Number

PORT Relation
Port Name
Country
Number of Docks
Port Manager

VISIT Relation
Cruise Number
Port Name
Country
Arrival Date
Departure Date

PASSENGER Relation
Passenger Number
Passenger Name
Social Security Number
Home Address
Telephone Number

VOYAGE Relation
Passenger Number
Cruise Number
Stateroom Number
Fare

Additional step:
Using MySQL Workbench, create the relational data model for the database for happy cruise lines, which includes 6 tables. (Note: Be sure primary and foreign keys are correct). Export the PDF version of your model for submission.

Then complete steps a – g.
Note: On step g, provide correct SQL syntax for finding the result to each item i – viii. i.e. Do not use the informal relational approach – looking for valid, correct SQL as described in Chapter 4.
a. Identify the candidate keys of each relation.
b. Identify the primary key and any alternate keys of each relation.
c. How many foreign keys does each relation have?
d. Identify the foreign keys of each relation.
e. Indicate any instances in which a foreign key serves as part of the primary key of the relation in which it is a foreign key. Why does each of those relations require a multi-attribute primary key?
f. Identify the relations that support many-to-many relationships, the primary keys of those relations, and any intersection data.
g. Using the informal relational command language described in this chapter, write commands to:
Note: On step g, provide correct SQL syntax for finding the result to each item i – viii. i.e. Do not use the informal relational approach – looking for valid, correct SQL as described in Chapter 4.
i. Retrieve the record for passenger number 473942.
ii. Retrieve the record for the port of Nassau in the Bahamas.
iii. List all of the Ships built by General Shipbuilding, Inc.
iv. List the port name and number of docks of every port in Mexico.
v. List the name and number of every ship.
vi. Who was the cruise director on cruise number 38232.
vii. What was the gross weight of the ship used for cruise number 39482?
viii. List the home address of every passenger on cruise number 17543.

Learn More

GU CS371 Database Design Week 6 Assignment Chapter 7

Fundamentals of Database Management Systems 2nd Edition
Chapter 7 Questions
Exercise 2 – Using MySQL Workbench, recreate the data model given to you but in a “well-structured” format. Note: This essentially means adding foreign keys. Be sure to choose key names that make sense.
Minicase 1 - Using MySQL Workbench, recreate the data model given to you but in a “well-structured” format. Note: This essentially means adding foreign keys. Be sure to choose key names that make sense.

Exercise:
2. Convert the Central Hospital entity-relationship diagram on the next page into a well-structured
relational database. (Instructions: Exercise 2 – Using MySQL Workbench, recreate the data model given to you but in a “well-structured” format. Note: This essentially means adding foreign keys. Be sure to choose key names that make sense.)

Minicase:
1. Happy Cruise Lines. Convert the Happy Cruise Lines entity-relationship diagram on the next page into a well structured relational database.
(Instructions: Minicase 1 - Using MySQL Workbench, recreate the data model given to you but in a “well-structured” format. Note: This essentially means adding foreign keys. Be sure to choose key names that make sense.)

Learn More

GU CS371 Database Design Week 7 Assignment Chapter 8 Final Project

Fundamentals of Database Management Systems 2nd Edition
Questions and Task:
Question 1
In your own words discuss the benefits of normalization.

Question 2
Do you think we should normalize our designs to higher levels than 3NF? Why or why not?

Question 3
There are 14 physical database design techniques discussed in chapter 8. The goal of a quality physical design is to improve performance while disrupting the logical design as little as possible. Pick a technique, which you believe to be the most beneficial in accomplishing the above goal, and explain your reasoning.

Question 4
Discuss the concept of an index and explain how they improve performance. Assignment "Final Project Overview" You just took a job with a University and have been asked to track some information about the courses they offer. The previous employee was using a spreadsheet to track this information. The University is expanding from three classes to three hundred classes. Enrollment is expected to increase from around 20 students to 5,000. Your supervisors are not very tech savvy and they simply ask you to continue maintaining the spreadsheet. Review the spreadsheet labeled final_project.xlsx.

Explain the issues that exist with maintaining the data in its current form.
Explain the process of migrating the data in its current form to a well formed data model by highlighting the following in detail:
Identify required attributes that need to be tracked
Identify functional dependencies Show the redesign in 1NF, 2NF, and 3NF (similar to figures 7.29, 7.31, 7.33)
Explain the benefits of the data in its new form.
Create the E-R diagram of your relational tables using MySQL Workbench.
Provide the SQL statement required to create at least one of your tables.
Provide the SQL statement required to delete at least one of your tables.
Provide the SQL statement required to create a view which includes Course #, Course Name, Time, and Days only.
Identify at least one index, which you believe would improve performance.
Explain your choice.
Propose some additional data items, which you believe may be beneficial to the University if tracked in your database (i.e. Descriptions, more info about the instructors and students, departments, etc).
Describe the steps required to implement these additions.
If you add a course description attribute to your course table, there may be some performance impacts imposed by the addition of this text field.
Discuss a physical design technique to improve the performance of the new course description field.

Learn More

CS362 Structured Query Language for Data Management Week 4 IP Extend the Design

Using your Week 1 IP as a starting point, extend the design to accommodate degree programs. The new design should incorporate the following functionalities:

1. A degree has a name and description.
2. What degree is the student working towards? A student can only work on one degree at a time.
3. What classes are necessary to obtain a specific degree?
4. Provide the DDL script to add the tables for the new design changes. Include an updated diagram that shows all the tables in the system.
5. Write the DML script to insert 3 test records in each of the tables in the system. This data will be necessary to write the queries in the next assignment.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Upload your document to the Submitted Tasks section.

Learn More

CS362 Structured Query Language for Data Management Week 5 IP Functions Stored Procedure and Indexes

This assignment requires you to take your extended design from Week 4 IP and add proper indexes, a function, and a stored procedure. Your changes will provide functionality used by the teachers for screens such as a grade book. Be sure any code is properly formatted and has appropriate comments.

Part 1: Function
Write a user-defined function (UDF) that calculates a student's GPA for a given time frame. Inputs are StudentId int, ClassStartDateStart datetime, and ClassStartDateEnd datetime. The output should be the student's GPA for all classes that were taken between ClassStartDateStart and ClassStartDateEnd. Also, supply the script to call this new function, passing it parameter values of your choice.

Part 2: Stored Procedure
Write the DDL script to make a stored procedure that returns data needed to display a grade book screen for a professor. The only input for the stored procedure is a ClassId. Outputs need to include student names and grades for all assignments as well as a calculated overall grade for the class for each student. Provide an example calling this new stored procedure, passing it parameter values of your choice. Include a screenshot of the output.

Part 3: Indexes
Provide a list of suggested indexes and the DDL script to create them. Include an explanation of the purpose of indexes and how you made your decision for the fields to include in your list of suggested indexes.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Upload your document to the Submitted Tasks.

Learn More