Search results for 'eco'

DAT/380 Week 5 LAB 5.7 - Implement supertype and subtype entities (Sakila)

Refer to the customer and staff tables of the Sakila database. These tables have many columns in common and represent similar entities. Convert the customer and staff entities into subtypes of a new supertype person:

In the center is the person entity, with primary key person_id and additional attributes first_name, last_name, email, active, and last_update. The person entity contains subtype entities staff and customer. The staff entity has primary key person_id and additional attributes picture, username, and password. The customer entity has primary key person_id and additional attribute create_date. Cardinality does not appear after the primary keys and attributes. On the left is the address entity, connected to the person entity by the belongs_to relationship. Belongs_to has cardinality 1(1) on the address side and M(0) on the person side. On the right is the store entity, connected to the person entity by the works_at relationship. Works_at has cardinality 1(1) on the store side and M(0) on the person side.

The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names:
All lower case
Underscore separator between root and suffix
Foreign keys have the same name as referenced primary key

Implement supertype and subtype entities as person, customer, and staff tables with primary key person_id.

Implement attributes as columns:
All columns are NOT NULL.
The person_id columns have data type SMALLINT UNSIGNED.
The last_update and create_date columns have data type TIMESTAMP.
The picture column has data type BLOB.
All other columns have data type VARCHAR(20).

Implement the dependency relationships between subtype and supertype entities as foreign keys:
The person_id columns of customer and staff become foreign keys referring to person.
Specify CASCADE actions for both relationships.

Implement the belongs_to and works_at relationships as foreign keys:
belongs_to becomes an address_id foreign key in person referring to address.
works_at becomes a store_id foreign key in staff referring to store.
Specify RESTRICT actions for both relationships.

The address and store tables, with primary keys address_id and store_id, are pre-defined in the zyLab environment. Foreign keys must have the same data types as the referenced primary keys:
address_id has data type SMALLINT UNSIGNED.
store_id has data type TINYINT UNSIGNED.

If you execute your solution with the Sakila database, you must first drop customer, staff, and all constraints that refer to these tables. Use the following statements with Sakila only, not in the zyLab environment:
DROP TABLE customer, staff;
ALTER TABLE payment
DROP CONSTRAINT fk_payment_customer,
DROP CONSTRAINT fk_payment_staff;
ALTER TABLE rental
DROP CONSTRAINT fk_rental_customer,
DROP CONSTRAINT fk_rental_staff;
ALTER TABLE store
DROP CONSTRAINT fk_store_staff;

DBM 405A Lab 3 Database Creation and Table Manipulation

I. OBJECTIVES
1. Understand and become familiar with Table Manipulation.

II. PARTS LIST
1. EDUPE Omnymbus Environment (https://devry.edupe.net:8300); and/or
2. MySQL (dev.mysql.com/downloads).

III. PROCEDURE
By now you have set up either the Omnymbus environment or the MySQL Server Community environment, or both. You may do the labs in this class on your own computer equipped with MySQL or you may use the MySQL environment hosted by the vendor Omnymbus. You may even use both.

Lab Procedure Continued (common to both environments)
1. Add a table named "Season" to your Baseball database, which consists of the following fields:
a. Season (Integer)
b. PlayDate (date)
c. HomeTeam (character 5)
d. HomeTeamScore (Integer)
e. AwayTeam (character 5)
f. AwayTeamScore (Integer)
2. Populate the table with 56 unique games where each plays the other 7 twice (once as the home team and once as the away team). Make the season equal to this year. Leave the scores blank and use any date for the PlayDate.
3. Create another table called "PastSeasons" by copying the structure of the table "Season" and copying all the data from "Season" into the new table.
4. Replace the Season field in the table Season with next year for all records.
5. Unload the Season table data to a comma-delimited file.
6. Load the comma-delimited file to PastSeasons table, appending the data to the data that is already there.

DBM 405A Lab 2 SQL Review

I. OBJECTIVES
1. Get access to a MySQL environment.
2. Familiarize yourself with the environment.

II. PARTS LIST
3. EDUPE Omnymbus Environment (https://devry.edupe.net:8300); and/or
4. MySQL (dev.mysql.com/downloads).

III. PROCEDURE
You may do the labs in this class on your own computer equipped with MySQL or you may use the MySQL environment hosted by the vendor Omnymbus. You may even use both.

Using MySQL on your own computer means downloading a version from dev.mysql.com/downloads and installing the database engine on your computer. This can be done easily and there are a number of resources on the web to help you with this process.

You may also use the MySQL environment hosted by Omnymbus by logging in with your credentials to https://devry.edupe.net:8300. 

Using the first approach will give you experience writing programs on a desktop computer and accessing a database. Using the second approach will give you experience writing applications that access a database in a cloud environment.

You are encouraged to use both approaches.

In the previous lab (Week 1), you have established a database environment (either MySQL Community Server or the Omnymbus environment). You may continue to use that environment or you my return to Week 1 to set up the alternate environment.

Lab Procedure Continued (common to both environments)
1. Use the database named “Baseball” created in last week’s lab.
2. Use the table named “Teams” which consists of the following fields:
a. TeamCode (character 5)
b. TName (character 20)
c. TAddress (character 30)
d. TCity (character 20)
e. TState (character 2)
f. TZip (character 9)
g. Wins (integer)
h. Losses (integer)
3. You should have a table with 8 teams.
4. Use the table named “Players”, which consists of the following fields:
a. FirstName (character 20)
b. LastName (character 30)
c. MidInit (character 1)
d. PAddress (character 30)
e. PCity (character 20)
f. PState (character 2)
g. PZip (character 9)
h. Phone (character 11)
i. Team (character 5)
5. You should have a table with 80 players (10 on each team). Players are identified by the Team field which matches the TeamCode in the Teams table.
6. Demonstrate the use of the WHERE clause by selecting and displaying only the players from one team.
7. Demonstrate the use of the COUNT function by determining how many players are on each team and displaying the results.
8. For each City, find the number of players who list their home in that city.
9. Demonstrate the ORDER BY clause by listing all the players in alphabetical order by last name within Team.

DBM 405A Lab 1 SQL Review

I. OBJECTIVES
a. Get access to a MySQL environment.
b. Familiarize yourself with the environment.

II. PARTS LIST
1. EDUPE Omnymbus Environment (https://devry.edupe.net:8300); and/or
2. MySQL (dev.mysql.com/downloads).

III. PROCEDURE
You may do the labs in this class on your own computer equipped with MySQL or you may use the MySQL environment hosted by the vendor Omnymbus. You may even use both.

Using MySQL on your own computer means downloading a version from dev.mysql.com/downloads and installing the database engine on your computer. This can be done easily and there are a number of resources on the web to help you with this process.

You may also use the MySQL environment hosted by Omnymbus by logging in with your credentials to https://devry.edupe.net:8300.

Using the first approach will give you experience writing programs on a desktop computer and accessing a database. Using the second approach will give you experience writing applications that access a database in a cloud environment.

You are encouraged to use both approaches.

Desktop MySQL Environment
1. Download the MySQL Community Server from the http://dev.mysql.com/downloads/ website.
2. Install MySQL on your computer.
3. Open the MySQL Environment on your computer.

Omnymbus Environment
1. Login to the MySQL Omnymbus Environment by following the instruction located in Doc Sharing (document titled “Login MySQL Omnymbus Environment”).

Lab Procedure Continued (common to both environments)
1. Create a database named “Baseball”.

2. Create a table named “Teams” and add the following fields:
a. TeamCode (character 5)
b. TName (character 20)
c. TAddress (character 30)
d. TCity (character 20)
e. TState (character 2)
f. TZip (character 9)
g. Wins (integer)
h. Losses (integer)

3. Populate the table with 8 teams.

4. Create a table named “Players” and add the following fields:
a. FirstName (character 20)
b. LastName (character 30)
c. MidInit (character 1)
d. PAddress (character 30)
e. PCity (character 20)
f. PState (character 2)
g. PZip (character 9)
h. Phone (character 11)
i. Team (character 5)

5. Populate the table with 80 players (10 on each team). For the team, use the TeamCode from the first table to identify the Team the player is on.
6. Display or list the data from both table to the screen or printer.

Bigger Vision of Athens Lab 6 Program to connect database

For this project - you will be writing a program (you may choose whatever programming language you want) to read in your database tables from Lab 5, and then print out the contents of each of the tables. Additionally, you must print at least one query each with the following clauses:
*JOIN (any version)
*WHERE
*ORDER BY

You must submit the following:
*Your program's source code
*The SQL commands you used
*Screenshots of the program's output

Scenario:
Bigger Vision of Athens You have been asked to develop a database system for the Bigger Vision of Athens Emergency Shelter.
BVoA is a small, community shelter with approximately 142 beds.
The basic goal is to provide hot meals, showers, laundry services, and a place to stay at night for the surrounding community in North Georgia.
The various business functions that the non-profit currently has are:
1. Guest management software: Stores information about guests of the shelter
2. Volunteer management software: Records information about the shelter's volunteers
3. Scheduling software: Assigns guests and volunteers to specific days of the month
4. Financial management software: Tracks the shelter inventory, financial resources, and operations of the shelter, including donor information
5. Administrative services software: Provide general management and support services for board members not directly related to shelter operations

Examples of shelter software operations include: scheduling guests for the night, scheduling volunteers in advance for certain days, clocking guests and volunteers in and out, recording meals, showers, and laundry services, writing up guests for rule violations, reviewing inventory levels of needed supplies, putting in requests for new supplies, logging board meeting notes, and saving donor information.
The staff at the shelter include 80 full-time personnel and 30 part-time personnel, such as: the executive director, board members, administrative staff, day workers, night workers, job counselors, social services staff, technical support staff, cooking staff, janitorial staff, and security staff.

Bigger Vision of Athens Lab 5 SQL SELECT statements

For this lab you will be using SQL SELECT statements (and depending on your structure, JOINs) to query your database tables. You will be turning in the results of the following queries:

1. List all Guests in the databases
2. List all Guests who stayed at least one time between January 3rd, 2019 and March 4th, 2019.
3. List all Inventory items and their current quantities and units when the quantity is less than 3
4. List the volunteers and what shift they worked most recently
5. List all donors in alphabetical order
6. List all donors who live in Atlanta and have donated at least $500 dollars.
7. List all donors who live in either Watkinsville or Athens who have made at least two donations.

If you don't have enough data in your created tables that will allow you to create these queries - add appropriate data to your tables before doing the queries.

Turn in the following:
*A text file containing the SQL commands you used for each question (labelled/comment for each question they satisfy).

*Screenshots for each question's results

Scenario:
Bigger Vision of Athens You have been asked to develop a database system for the Bigger Vision of Athens Emergency Shelter.
BVoA is a small, community shelter with approximately 142 beds.
The basic goal is to provide hot meals, showers, laundry services, and a place to stay at night for the surrounding community in North Georgia.
The various business functions that the non-profit currently has are:
1. Guest management software: Stores information about guests of the shelter
2. Volunteer management software: Records information about the shelter's volunteers
3. Scheduling software: Assigns guests and volunteers to specific days of the month
4. Financial management software: Tracks the shelter inventory, financial resources, and operations of the shelter, including donor information
5. Administrative services software: Provide general management and support services for board members not directly related to shelter operations

Examples of shelter software operations include: scheduling guests for the night, scheduling volunteers in advance for certain days, clocking guests and volunteers in and out, recording meals, showers, and laundry services, writing up guests for rule violations, reviewing inventory levels of needed supplies, putting in requests for new supplies, logging board meeting notes, and saving donor information.
The staff at the shelter include 80 full-time personnel and 30 part-time personnel, such as: the executive director, board members, administrative staff, day workers, night workers, job counselors, social services staff, technical support staff, cooking staff, janitorial staff, and security staff.

Bigger Vision of Athens Lab 4 Create Table in Oracle

For this lab we will be taking the final set of 3NF tables developed from our normalization process in Lab 3 and creating them into Oracle Database (through installing or using an online tool).

(Please review the tutorials posted in Week 9 to check your work from Lab 3 and get it into the correct form)
You will use SQL and the CREATE TABLE command to create each table with its various attributes (columns).
You will then use the INSERT INTO command to add 5 rows of sample data to populate each table.
After creating the tables and entering the data, use the SELECT * FROM {tablename} command to list all of the data in each table. You can do a screen capture to save the table data.

Turn in the following:
*A text file containing all the SQL commands you used to create each tables, insert the data, and select it in order.
*Screenshots for each of your tables with all of the data

Scenario:
Bigger Vision of Athens You have been asked to develop a database system for the Bigger Vision of Athens Emergency Shelter.
BVoA is a small, community shelter with approximately 142 beds.
The basic goal is to provide hot meals, showers, laundry services, and a place to stay at night for the surrounding community in North Georgia.
The various business functions that the non-profit currently has are:
1. Guest management software: Stores information about guests of the shelter
2. Volunteer management software: Records information about the shelter's volunteers
3. Scheduling software: Assigns guests and volunteers to specific days of the month
4. Financial management software: Tracks the shelter inventory, financial resources, and operations of the shelter, including donor information
5. Administrative services software: Provide general management and support services for board members not directly related to shelter operations

Examples of shelter software operations include: scheduling guests for the night, scheduling volunteers in advance for certain days, clocking guests and volunteers in and out, recording meals, showers, and laundry services, writing up guests for rule violations, reviewing inventory levels of needed supplies, putting in requests for new supplies, logging board meeting notes, and saving donor information.
The staff at the shelter include 80 full-time personnel and 30 part-time personnel, such as: the executive director, board members, administrative staff, day workers, night workers, job counselors, social services staff, technical support staff, cooking staff, janitorial staff, and security staff.

Bigger Vision of Athens Lab 3 Normalization

For this lab, we will be completing the Normalization step for several of your entities from your E/R diagram.
You will normalize the following entities to 3rd normal form:
GUEST
INVENTORY
VOLUNTEER
SCHEDULE
DONOR
(Show ALL steps, from 1st normal form to 2nd normal form to 3rd normal form)
After going through your steps of normalization, add in your Foreign keys based on your cardinalities from your E/R diagram. Be sure you include your attributes in your table from Lab 2.

Scenario:
Bigger Vision of Athens You have been asked to develop a database system for the Bigger Vision of Athens Emergency Shelter.
BVoA is a small, community shelter with approximately 142 beds.
The basic goal is to provide hot meals, showers, laundry services, and a place to stay at night for the surrounding community in North Georgia.
The various business functions that the non-profit currently has are:
1. Guest management software: Stores information about guests of the shelter
2. Volunteer management software: Records information about the shelter's volunteers
3. Scheduling software: Assigns guests and volunteers to specific days of the month
4. Financial management software: Tracks the shelter inventory, financial resources, and operations of the shelter, including donor information
5. Administrative services software: Provide general management and support services for board members not directly related to shelter operations

Examples of shelter software operations include: scheduling guests for the night, scheduling volunteers in advance for certain days, clocking guests and volunteers in and out, recording meals, showers, and laundry services, writing up guests for rule violations, reviewing inventory levels of needed supplies, putting in requests for new supplies, logging board meeting notes, and saving donor information.
The staff at the shelter include 80 full-time personnel and 30 part-time personnel, such as: the executive director, board members, administrative staff, day workers, night workers, job counselors, social services staff, technical support staff, cooking staff, janitorial staff, and security staff.

MIS582 iLab 1 SQL Queries Using MySQL

iLab Overview
Scenario/Summary
The purpose of this iLab is to prepare for the first phase of the Course Project. This week, you will learn to create and run SQL SELECT queries from a script in the MySQL database. You will need to create a database in MySQL via Omnymbus, run a SQL script to create tables and insert data, create and execute a script containg SQL SELECT queries using the STUDENT table.
Please ensure that you can connect to MySQL/Omnymbus via the account your Professor has emailed to you. Please consult with the document titled MySQLOmnymbusSupport.docx located in the Doc-Sharing folder titled Omnymbus Tutorial Files for instructions on how to get help for any issues that you are having with the MySQL/Omnymbus Environment.
Due Midnight Mountain Time attached to the Week 1 DropBox. OR let me know if you are going to be delayed!
SQL file named Lab1_Query.sql containing SELECT statements
Text document named Lab1_Output.txt containing labeled output
Upload files to the Week 1 iLab Dropbox.
Please do not zip the files

Required Software
Omnymbus – MySQL at the edupe database server.
Access the software at https://devry.edupe.net:8300
If you have any problems, connection, password, technical, etc ONLY your instructor can assist. Using any other Help facility will delay the resolution of the problem as the request is forwarded in all cases to the instructor.

STEP 1: Logging in to Omnymbus
Look at your email account to obtain the MySQL/Omnymbus account and password that your Professor has emailed to you.
To help you log into MySQL Omnymbus environment, download the tutorial Login MySQL Omnymbus Environment in the Doc-Sharing folder titled "Omnymbus Tutorial Files".

STEP 2: Create a Database and modify your script to reference your Database
Create a MySQL database:
Download the tutorial Creating a Database in MySQL Omnymbus Environment from the folder in Doc-Sharing titled Omnymbus Tutorial Files. Follow the steps to create a database in MySQL, especially paying attention to the database naming conventions specified in the tutorial.
Download the Wk1LoadDatabaseScript.sql file from Doc Sharing in the folder titled iLab Documents.

STEP 3: Running script file in MySQL, create SQL
Download the tutorial Running SQL Scripts in MySQL Omnymbus environment from the folder in Doc-Sharing titled Omnymbus Tutorial Files. Follow those steps and execute the Wk1LoadDatabaseScript.sql file to create the tables and to insert data into them; and then download and run the script file with the queries iLab1QueryScript.sql. This script has the first few queries and it is intended that you should finish it. A demonstration in the Week 1 Live Lecture will be provided.

Create SQL SELECT Queries:
ALWAYS and only use a plain text editor such as Notepad! All word processors will insert hidden tags that confuse database processors.
Using the data in the Student table in the database, expand the supplied starter SQL script file named iLab1QueryScript.sql, containing queries to execute each of the tasks below.
To reference, learn and apply MySQL's own dialect of the SQL language to this iLab, browse through the file M10C_KROE8352_13_SE_WC10C.pdf in the Doc-Sharing folder titled My SQL Documents.
Save the completed results by copying and pasting to a file named iLab1_Results.txt
An incomplete example is provided in the Doc Sharing.

Query1
Write a SQL statement to display Student’s First and Last Name.

Query2
Write a SQL statement to display the Major of students with no duplications. Do not display student names.

Query3
Write a SQL statement to display the First and Last Name of students who live in the Zip code 82622

Query4
Write a SQL statement to display the First and Last Name of students who live in the Zip code 97912 and have the major of CS.

Query5
Write a SQL statement to display the First and Last Name of students who live in the Zip code 82622 or 37311. Do not use IN.

Query6
Write a SQL statement to display the First and Last Name of students who have the major of Business or Math. Use IN.

Query7
Write a SQL statement to display the First and Last Name of students who have the Class greater than 1 and less than 10. Use the SQL command BETWEEN.

Query8
Write a SQL statement to display the First and Last Name of students who have a last name that starts with an S.

Query9
Write a SQL statement to display the First and Last Name of students having an a in the second position of their first names.

Query10
Write a SQL expression to display each Status and the number of occurrences of each status using the Count(*) function; display the result of the Count(*) function as CountStatus. Group by Status and display the results in descending order of CountStatus.
Note: there are examples very similar to these in the Kroenke text

Rubric
Queries that are correct will be awarded the number of points shown below:
4 points: Query 1
5 points: Query 2 – 9
6 points: Query 10
The following rubrics will be used for incorrect queries:
0 points: Query was not turned in with the assignment.
-4 points: Query will not run.
-3 points: Query runs but is incorrect because query required a WHERE clause to meet requirements which was not included.
-2 points: Query runs but is incorrect because WHERE clause contained errors, gives popup for user input, or only meets partial requirements.

Please Make sure all requirements are met!