7. Using the provided schema of a Purchase Order Administration database, write the following queries in SQL.

(In the schema, bold attributes are primary keys and italicized attributes are foreign keys.)

SUPPLIER (SUPNR, SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS)

SUPPLIES (SUPNR, PRODNR, PURCHASE_PRICE, DELIV_PERIOD)

PRODUCT (PRODNR, PRODNAME, PRODTYPE, AVAILABLE_QUANTITY)

PO_LINE (PONR, PRODNR, QUANTITY)

PURCHASE_ORDER (PONR, PODATE, SUPNR)

7d) Write a nested SQL query to retrieve the supplier number, supplier name, and supplier status of each supplier who has a higher supplier status than supplier number 21.

7e) Write a nested SQL query using the keyword IN to retrieve the supplier name of each supplier who supplies more than five products.

7f) Write an SQL query that retrieves all pairs of suppliers who supply the same product, along with their product purchase price if applicable.

7g) Create a view, SUPPLIEROVERVIEW, which retrieves for each supplier the supplier number, the supplier name, and the total quantities ordered. Once created, query this view to retrieve suppliers for which the total ordered quantity exceeds 30.

Please create the following tables for a tool rental database with appropriate

primary keys & foreign keys.

Assumptions:

1. Each tool belongs to a category.

2. Each category may have a parent category but the parent category should not have

a parent category (so at most two levels). E.g., a Tool A belongs to the electric mower,

and electric mowers belong to mowers. 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:

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_Table:

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_Table:

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

- Insert at least three rows of data to each table. Make sure you keep the

primary key and foreign key constraints.

-Write an anonymous PL/SQL program to compute the sum of 2, 4, 6, 8, 10.

You must use a loop. Tip: consider how to update your loop variable.

- Print out the estimated charge for rental ID 1 if the customer returns the tool in

time. The charge is computed by the price in the price_tool table * number of units the

customer plans to rent. E.g., if a customer rents a tool hourly for 5 hours, and the hourly rate for the tool is $6, the estimated charge should be $30.

PHP

The database should have at least two tables with, at minimum, the following fields:

Create a PHP page that will extract a customer’s bill amount and the amount paid. Then it will calculate the amount due. If the amount due is greater than 0, an email should be generated and sent to the customer with that amount in the message. If the amount due is zero or there is a credit, generate a thank-you email that thanks the customer for his or her payment and expresses Lee’s wishes for continued business with this customer. Name the main page sendBill.php and be sure to include all the necessary accompanying files when you submit your work.

lab requires you to use Oracle VIEW to implement a virtual database on DBSEC schema, for example, on CUSTOMER table. Your task is to develop a single SQL script that will perform all the following tasks:

Table created for this assignment is listed below:

create table CUSTOMER_VPD(

SALES_REP_ID NUMBER(4),

CUSTOMER_ID NUMBER(8) NOT NULL,

CUSTOMER_SSN VARCHAR(9),

FIRST_NAME VARCHAR(20),

LAST_NAME VARCHAR(20),

ADDR_LINE VARCHAR(40),

CITY VARCHAR(30),

STATE VARCHAR(30),

ZIP_CODE VARCHAR(9),

PHONE VARCHAR(15),

EMAIL VARCHAR(80),

CC_NUMBER VARCHAR(20),

CREDIT_LIMIT NUMBER,

GENDER CHAR(1),

STATUS CHAR(1),

COMMENTS VARCHAR(1025),

USER_NAME VARCHAR(30)

);

Tasks:

Populate the CUSTOMER_VPD table with four rows of records. Pay attention to the column CTL_UPD_USER,

Create a VIEW named as MY_VIEW to display only rows that belong to the logged in user

Grant SELECT and INSERT privilege on MY_VIEW to DBSEC_CLERK

Insert one row of data into MY_VIEW as DBSEC_CLERK by using the following data

Verify your data insertion by query MY_VIEW. You (as DBSEC_CLERK) should only see one row of data you have inserted. This signifies the success of your implementation.

DATABASE MANAGEMENT SYSTEMS

After looking at the material in Section 2, go ahead and answer the following questions:

• What is a foreign Key? (1 Point)
• What is a composite attribute? Give an example (1 Points)
• What is a multivalued attribute? Give an example (1 Points)
• What is a relationship Modality? What is a relationship Cardinality? (2 Point)

(Make sure you understand the difference between the two)

• Open MySql workbench and build an ERD with the following Entities, Attributes and Relationships.

We want to track historical data of Soccer players. We want to track current data for clubs, Coaches and Soccer Balls.

A soccer player can only play for one club but over time a player would have played for one or many clubs. A club has many players.

Some players are Captains and supervise other players.

A club can have one or many Coaches, a Coach can only coach one team.

A Team has one or many soccer balls, a Soccer ball can only belong to one team. (10 Points)

Consider the database of a car rental company that contains three tables drivers, cars and reservation tables.

Drivers:                                           Reservation:                              Cars:

1. Write DDL statements to create the tables

Drivers(Dno, Dname, age)

Reservation(Dno, Cno, Day)

Cars(Cno, Cmake, Color)

Where:

• no field could be empty except the age of the Driver.
• The company does not own more than one car from one maker     
• the colors of the cars should not be other than the following group (blue, white, red , green)

2. Write Queries for the following:
   1. Insert the sample data shown above into the tables

3. Write Queries for the following:
   1. Find the names of drivers who have reserved car no 103.
   2. Find the names of Drivers who have reserved red or Green cars.( use IN, UNION, =SOME/=ALL, and JION    each in separate query)For this segment, 4 responses are required, using the operations shown in parentheses
   3. Find the driver Dno of drivers with age over 20 who have not reserved a read car.
   4. Find the names of drivers who have reserved all cars.
   5. List the name of the drivers along with the day they rent cars in descending order
   6. List the name of the drivers whom rent more than 3 times along with the number of the times.
   7. Change the age of Alice to 18 years old
   8. Delete the information of the drivers younger than 20 years old

A database schema consisting of three relations STUDENT, COURSE, and STAFF is created as follows:

CREATE TABLE STUDENT (STU_ID CHAR(4),

STUDENT_NAME CHAR(20),

ADDRESS CHAR(20),

BIRTHDATE DATE,

GENDER CHAR(6));

CREATE TABLE COURSE (COURSE_ID CHAR(6),

COURSE_TITLE CHAR(20),

STAFF_ID CHAR(3),

SECTION NUMBER(2));

CREATE TABLE STAFF (STAFF_ID CHAR(3),

STAFF_NAME CHAR(20),

GENDER CHAR(6),

DEPARTMENT CHAR(20),

BOSS_ID CHAR(3)

SALARY NUMBER(8,2));

Write down SQL statement for each query below:

1) Find out the information of staff members who are female and earn either below $5,000 or above $30,000. [5 marks] 2) List all staff members who are not in the Accounting nor the History department. [5 marks] 3) List all students whose name contains the substring "JONES". List the females before the males in chronological order (by birthdate). [6 marks] 4) List all the courses taught by Raymond J. JOHNSON. [6 marks] 5) Find the names of all staff members who earn more than their bosses. [8 marks] 6) List all staff members who are either in the same department as Amy Dancer or Jack Nelson. [8 marks] 7) Find the names of the staff members who make more money than every member of the Accounting department. [7 marks] 8) Find the average salary for each department with more than one staff member. [5 marks]

Imagine you are tasked with designing a database that encompasses the Florida Tech Learning Management System (LMS) that you use on a daily basis. The following requirements hold:

• Typical online student enrollment is 3,000 annually
• Access is password protected
• There should be a feature to create new accounts with different roles
• A role (faculty, student, staff) may have access to only certain features
• Students can be enrolled in multiple courses
• Faculty can teach multiple courses/sections
• Student records must be kept indefinitely
• Users should have near 24/7 access (with one hour of downtime allotted per month)
• The most frequent operations from a student perspective are posting discussions, taking exams, uploading assignments, checking grades, and emails
• The most frequent operations for faculty are posting discussions, changing course content, grading, and emails
• The most frequent operations for staff are to enroll students, create new sections, and assign sections to faculty

What would be your initial design? Provide both a logical and physical approach. For your logical suggestion, provide an E-R model of how the system might look and then provide support that it will be efficient given the operations. For the physical design, provide an outline of strategies and infrastructure you would put into place to support the business requirements.

Using draw.io diagram tool, design a database schema for an application that tracks and manages students’ applications for a university. Make sure to create proper primary key for each entity. Add relations and specify cardinalities.

• All entities and attributes must be named using the underscore convention. Highlight primary keys in red
• List any assumptions you have made

• Use diamonds and the association on the arrow.

• THINGS TO KEEP IN MIND :

• The university needs to keep track of students’ application. The application contains information about student: name, address, phone number, DOB, SSN, and generate a unique ID for each student.
• Education records has to be submitted as well. The education records contain information about previous degrees, GPA, name of school attended and dates ( from – to dates)
• Test scores are also submitted along with applications. The student can submit more than one test score. The test score consist of test type, date and score.
• The student should also specify which major they are applying to, for what term
• The university assign one employee from the admission office to process the application. The employee will handle multiple application at the same time.
• Applications can be submitted to different departments.

You need to design a Web Server, Database Server and a Backup server.

If you had to choose from the following list of resources which ones would you place a priority on and state why you would do so. List these for each server type. Hint: You need to think about the functionality of the server. Based on this information, which resource would you emphasize on the most to increase the performance of the server.

• CPU utilization and speed
• Multiprocessing
• Memory
• Cache
• Storage capacity
• Hard drive seek and access time
• Network Bandwidth