Garden Glory Project Questions Assume that Garden Glory designs a database with the following tables:

OWNER (OwnerID, OwnerName, OwnerEmail, OwnerType)

OWNED_PROPERTY (PropertyID, PropertyName, PropertyType, Street, City, State, Zip, OwnerID)

GG_SERVICE (ServiceID, ServiceDescription, CostPerHour);

EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel)

PROPERTY_SERVICE ( PropertyServiceID , PropertyID , ServiceID, ServiceDate , EmployeeID, HoursWorked)

The referential integrity constraints are:

OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER

PropertyID in PROPERTY_SERVICE must exist in PropertyID in OWNED_PROPERTY

ServiceID in PROPERTY_SERVICE must exist in ServiceID in GG_SERVICE

EmployeeID in PROPERTY_SERVICE must exist in EmployeeID in EMPLOYEE

Assume that OwnerID in OWNER, PropertyID in PROPERTY, and EmployeeID in EMPLOYEE are surrogate keys with values as follows:

OwnerID Start at 1 Increment by 1

PropertyID Start at 1 Increment by 1

ServiceID Start at 1 Increment by 1

EmployeeID Start at 1 Increment by 1

PropertyServiceID Start at 1 Increment by 1

Sample data are shown in Figure 3-38, Figure 3-39, Figure 3-40, Figure 3-41, and Figure 3-42. OwnerType is either Individual or Corporation. PropertyType is one of Office, Apartments, or Private Residence. ExperienceLevel is one of Unknown, Junior, Senior, Master or SuperMaster. These tables, referential integrity constraints, and data are used

Sample Data for Garden Glory OWNER Table OwnerID OwnerName OwnerEmailAddress OwnerType

1 Mary Jones [email protected] Individual

2 DT Enterprises [email protected] Corporation

3 Sam Douglas [email protected] Individual

4 UNY Enterprises [email protected] Corporation

5 Doug Samuels [email protected] Individual

Sample Data for Garden Glory OWNED_PROPERTY Table PropertyID PropertyName PropertyType Street City State ZIP OwnerID

1 Eastlake Building Office 123 Eastlake Seattle WA 98119 2

2 Elm St Apts Apartments 4 East Elm Lynwood WA 98223 1

3 Jefferson Hill Office 42 West 7th St Bellevue WA 98007 2

4 Lake View Apts Apartments 1265 32nd Avenue Redmond WA 98052 3

5 Kodak Heights Apts Apartments 65 32nd Avenue Redmond WA 98052 4

6 Jones House Private Residence 1456 48th St Bellevue WA 98007 1

7 Douglas House Private Residence 1567 51st St Bellevue WA 98007 3

8 Samuels House Private Residence 567 151st St Redmond WA 98052 5

as the basis for the SQL statements you will create in the exercises that follow. If possible, run these statements in an actual DBMS, as appropriate, to obtain your results. Name your database GARDEN_GLORY. Use data types consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types using either the MySQL, Microsoft SQL Server, or Oracle Database data types shown in Figure 3-5. For each SQL statement you write, show the results based on your data. Write SQL statements and answer questions for this database as follows:

FIGURE 3-40

Sample Data for Garden Glory EMPLOYEE Table EmployeeID LastName FirstName CellPhone ExperienceLevel

1 Smith Sam 206-254-1234 Master

2 Evanston John 206-254-2345 Senior

3 Murray Dale 206-254-3456 Junior

4 Murphy Jerry 585-545-8765 Master

5 Fontaine Joan 206-254-4567 Senior

FIGURE 3-41

Sample Data for Garden Glory GG_SERVICE Table ServiceID ServiceDescription CostPerHour

1 Mow Lawn 25.00

2 Plant Annuals 25.00

3 Weed Garden 30.00

4 Trim Hedge 45.00

5 Prune Small Tree 60.00

6 Trim Medium Tree 100.00

7 Trim Large Tree 125.00

FIGURE 3-42

Sample Data for Garden Glory PROPERTY_SERVICE Table PropertyServiceID PropertyID ServiceID ServiceDate EmployeeID HoursWorked

1 1 2 2019-05-05 1 4.50

2 3 2 2019-05-08 3 4.50

3 2 1 2019-05-08 2 2.75

4 6 1 2019-05-10 5 2.50

5 5 4 2019-05-12 4 7.50

6 8 1 2019-05-15 4 2.75

7 4 4 2019-05-19 1 1.00

8 7 1 2019-05-21 2 2.50

9 6 3 2019-06-03 5 2.50

10 5 7 2019-06-08 4 10.50

11 8 3 2019-06-12 4 2.75

12 4 5 2019-06-15 1 5.00

13 7 3 2019-06-19 2 4.00

G. Write an SQL statement to list LastName, FirstName, and CellPhone of employees who have worked on a property in Seattle. Use a subquery.

H. Answer question G but use a join using JOIN ON syntax. What are the consequences of using (or not using) the DISTINCT keyword in this version of the query?

I. Write an SQL statement to list LastName, FirstName, and CellPhone of employees who have worked on a property owned by a corporation. Use a subquery.

will you please explain thank you!

Database Systems

Lab Exercises

1. Populate the Dept table with the following:

2. Populate the Faculty table with the following:

3. Change the last name of Faculty Id = 767636 to "Ameen".
4. Change the Dept of Mr. Ali to 'MGT'.
5. Delete Faculty Id 557899.
6. Delete Dept_Code 'SWE' on Dept table.

Use the Manufacturing database from “Excel Databases.xls” on Blackboard. Use Excel to develop a multiple regression model to predict Cost of Materials by Number of Employees, New Capital Expenditures, Value Added by Manufacture, and End-of-Year Inventories.

Locate the observed value that is in Industrial Group 12 and has 7 employees. Based on the model and the multiple regression output, what is the corresponding residual of this observation? Write your answer as a number, round to 2 decimal places.

**Answer should be 542.83, I just need to know how**

Question #2 - Review 'Cars Database'. From this data set develop the following: a. One bivariate regression b. One multiple regression c. Provide an effective chart displaying each of the two regressions. Charts should be presentation ready with effective title and labels. d. Provide a brief description of each of the two regressions (please use a text box in your Excel spreadsheet for your descriptions). Also, be sure to correctly identify and describe your independent and dependent variables, linear equation (answer), R, R square, F statistics, t statistic, other.

Use the International Stock Market database from “Excel Databases.xls” on Blackboard. Use Excel to develop a multiple regression model to predict the DJIA by the Nasdaq, the S&P 500, the Nikkei, the Hang Seng, the FTSE 100, and the IPC. Performing a stepwise regression analysis at a 5% level of significance, add the independent variable from Step 2 and continue to perform the stepwise regression analysis until you have reached the best linear model. Which independent variables are in the best linear model? Check all that apply.

Excel Data Here

https://drive.google.com/file/d/1TQG5r2wzLGk--75whZXyb0SDTHZTWS0S/view?usp=sharing

A: Nasdaq

B: S&P 500

C: Nikkei

D: Hang Seng

E: FTSE 100

F: IPC

Choose All Answers That Apply

Problem 1. Please create the following tables for a tool rental database with appropriate primary keys & foreign keys. [30 points]

Assumptions:

1. Each tool belongs to a category.
2. Each category may have a parent category but the parent category should not have parent category (so at most two levels). E.g., a Tool A belongs to electric mower, and electric mower belongs to mower. Mower has no parent category.
3. Each tool can be rented at different time units. The typical time units are hourly, daily, and weekly. There is a different price for each time unit and tool combination. E.g., tool A may be rented at $5 per hour, $30 per day, and $120 per week.
4. Each customer can rent a tool for a certain number of time units. If the tool is returned late a late fee will be charged.

The list of tables is:

Tables:

Cust Table:

cid, -- customer id

cname, --- customer name

cphone, --- customer phone

cemail, --- customer email

Category table:

ctid, --- category id

ctname, --- category name

parent, --- parent category id since category has a hierarchy structure, power washers, electric power washers, gas power washers. You can assume that there are only two levels.

Tool:

tid, --- tool id

tname, --- tool name

ctid, --- category id, the bottom level.

quantity, --- number of this tools

Time_unit table allowed renting unit

tuid, --- time unit id

len, --- length of period, can be 1 hour, 1 day, etc.

min_len, --- minimal #of time unit, e.g., hourly rental but minimal 4 hours.

Tool_Price:

tid, --- tool id

tuid, --- time unit id

price, -- price per period

Rental:

rid, --- rental id

cid, --- customer id

tid, --- tool id

tuid, --- time unit id

num_unit, --- number of time unit of rental, e.g., if num_unit = 5 and unit is hourly, it means 5 hours.

start_time, -- rental start time

end_time, --- suppose rental end_time

return_time, --- time to return the tool

credit_card, --- credit card number

total, --- total charge

• • Relational Schema

Create a relational database schema consisting of the four relation schemas representing various entities recorded by a furniture company.  

Write CREATE TABLE statements for the following four relation schemas. Define all necessary attributes, domains, and primary and foreign keys.

Customer(CustomerID, Name, Address)

FullOrder(OrderID, OrderDate, CustomerID)

Request(OrderID, ProductID, Quantity)

Product(ProductID, Description, Finish, Price)

You should assume the following: Each CustomerID is a number with at most three digits, each OrderID is a number with at most five digits, and each ProductID is a number with at most two digits. Each Name is at most 25 characters long, and each Address is at most 50 characters long. Each Description is at most 28 characters long, and each Finish is at most 10 characters long. Quantity and Price are integer and float respectively.

In this schema, primary and foreign keys are not marked. Identify primary and foreign keys and other custom constraints. Declare PK and FK in your CREATE TABLE statement and custom constraints as follows:

• Each Price is a non-negative value (zero is allowed) representing a price in dollars and cents that cannot exceed 999.99.
• Each Quantity is a positive whole number between 1 and 100, inclusive.

Populate the database as follows:

CUSTOMER:

CUSTOMERID NAME ADDRESS

---------------------- ------------------------------ --------------------

2 CASUAL FURNITURE PLANO, TX

6 MOUNTAIN GALLERY BOULDER, CO

FULLORDER:

ORDERID ORDERDATE CUSTOMERID

---------------------- ------------------------- ----------------------

1006 24-MAR-10 2

1007 25-MAR-10 6

1008 25-MAR-10 6

1009 26-MAR-10 2

PRODUCT:

PRODUCTID DESCRIPTION FINISH PRICE

---------------------- ------------------------------ ---------- ----------

10 WRITING DESK OAK 425

30 DINING TABLE ASH 600

40 ENTERTAINMENT CENTER MAPLE 650

70 CHILDRENS DRESSER PINE 300

REQUEST:

ORDERID PRODUCTID QUANTITY

---------------------- ---------------------- ----------------------

1006 10 4

1006 30 2

1006 40 1

1007 40 3

1007 70 2

1008 70 1

1009 10 2

1009 40 1                  

Remember to insert the records containing primary key values before inserting the records containing the foreign keys that reference those values (or your insert statements will fail).

Display the contents of each table by adding four statements of the form SELECT * FROM TABLE_NAME ; to the end of your script, where TABLE_NAME is substituted by the four table names above.

In order to avoid conflicts, include DROP TABLE commands for all four tables before your CREATE TABLE statements. Either drop tables containing foreign keys before the tables containing the referenced primary keys, or use CASCADE CONSTRAINTS. Verify that the tables have been defined correctly before going on to the next step.

Run the complete script. Be sure that that the contents of the four tables are displayed correctly by your script.

Submit a Q1.sql file.

Note:

You must code this schema using ‘Create Table’ statements . You should not use any other tool except Oracle SQLDeveloper. No points will be given for auto-generated schemas using other 3^rd party tools or web-based editors.

Internet Case 20.4 – Using the SEC EDGAR Database

(MUST POST FIRST) Initial Post – As an employee, write an internal memo to your manager addressing the following:

Visit the home page of The Securities & Exchange Commission at the following address: www.sec.gov

Use EDGAR to locate the most recent 10-K by researching an automotive company of your choice on the Internet and examine its consolidated income statement.

WRITE A MEMORANDUM INCLUDING

1. In the most recent year reported, what percentage of the company’s total revenue was from financial services?

2. In the most recent year reported, what was the company’s gross profit as a percentage of automotive sales?

3. How might the company’s sales mix (e.g., trucks, cars, sport utility vehicles, etc.) influence its profitability?

Go onto PsychInfo (or some other database) and find a study that uses archival data. Give me a very brief (two to three sentence) synopsis of the study. What did they look at, and what did they find? Make sure you look at an actual peer-reviewed study (and not just an article that tells you what archival research studies are!)

PLEASE GIVE ME A DIFFERENT ANSWER THAN THE ONE GIVEN BEFORE (I mean, this questions was already answered, but I need a different answer than the one already given.) if the question is unclear, please tell me how it is unclear.

Internet Case 20.4 – Using the SEC EDGAR Database

(MUST POST FIRST) Initial Post – As an employee, write an internal memo to your manager addressing the following:

Visit the home page of The Securities & Exchange Commission at the following address:  www.sec.gov

Use EDGAR to locate the most recent 10-K by researching an automotive company of your choice on the Internet and examine its consolidated income statement.

I CHOOSE GENERAL MOTORS

In the most recent year reported, what percentage of the company’s total revenue was from financial services?

In the most recent year reported, what was the company’s gross profit as a percentage of automotive sales?

How might the company’s sales mix (e.g., trucks, cars, sport utility vehicles, etc.) influence its profitability?