Use Excel to develop a regression model for the Hospital Database (using the “Excel Databases.xls” file on Blackboard) to predict the number of Personnel by the number of Births. Perform a test of the overall model, what is the value of the test statistic? Write your answer as a number, round your answer to 2 decimal places.

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

You have been given the following specifications for a simple database about the requests for software that staff members make for their units (note that primary keys are shown underlined, foreign keys in bold). You should run your SQL to demonstrate that it works correctly, and paste in the statements used plus the output from Oracle.

LAB (RoomNo, Capacity)

SOFTWARE (SoftwareID, SoftwareName, Version)

REQUEST (SoftwareID, RoomNo, RequestDate, TeachingPeriod, Progress)

Based on the table specifications provided, answer the following questions. Each question is worth 3 marks.

a. Give the SQL to create the LAB and SOFTWARE tables. Choose appropriate data types. None of the attributes should be allowed to be null. Include the primary key constraints.

b. Give the SQL to create the REQUEST table. Use appropriate data types, and include the primary key and foreign key constraints. Referential integrity should be set such that if a lab is deleted from the database, any requests that exist for that lab are also deleted.

c. Give the SQL to add a record to each of the tables: LAB, SOFTWARE and REQUEST. Make up your own data (you may wish to add lab 350.2.006 to the LAB table for testing in part (e) later).

d. Give the SQL to create a constraint to the REQUEST table to restrict the possible values of Progress to the following 5: Requested, Installed, Function Testing, User Acceptance Testing, and Deployed.

e. Give the SQL to record the fact that the capacity of lab 350.2.006 has increased by 5.

1. Using the sample.sql script, create the sample database in MySQL. Submit the MySQL interactive screen that results.

create database sample;
use sample;
create table customer (custno int auto_increment primary key, firstname varchar(20), middle varchar(20), lastname varchar(20), address varchar(60), telnum1 varchar(10), telnum2 varchar(10), telnum3 varchar(10), pin varchar(6), email varchar(30));
create table accttype (id int primary key, type varchar(10));
insert into accttype (id, type) values (1,'check');
insert into accttype (id, type) values (2,'save');
insert into accttype (id, type) values (3,'cd');
insert into accttype (id, type) values (4,'visa');
insert into accttype (id, type) values (5,'debit');
insert into accttype (id, type) values (6,'home');
create table account (acctno int auto_increment primary key, custno int, type int, balance double, index acpair(acctno,custno), foreign key (custno) references customer(custno), foreign key (type) references accttype(id));
insert into customer (firstname,middle,lastname,address,telnum1,pin,email) values('John','Quincy','Adams','PO Box 1234, Allentown, PA 18101','6102561034','2564','[email protected]');
insert into customer (firstname,middle,lastname,address,telnum1,telnum2,pin,email) values('Richard','Milhouse','Nixon','120 Union Avenue, Bethlehem, PA 18018','6102111210','4843201457','9873','[email protected]');
insert into customer (firstname,middle,lastname,address,telnum1,telnum2,pin,email) values('David','Dwight','Eisenhower','34 Main Street, Folgelsville, PA 18025','6104561234','4849871200','63712','[email protected]');
insert into account (custno, type, balance) values (1,1,1356.75);
insert into account (custno, type, balance) values (1,2,10000.00);
insert into account (custno, type, balance) values (1,3,50000.00);
insert into account (custno, type, balance) values (1,4,129.13);
insert into account (custno, type, balance) values (1,5,0.0);
insert into account (custno, type, balance) values (2,1,121.19);
insert into account (custno, type, balance) values (2,2,8194.10);
insert into account (custno, type, balance) values (2,4,2015.99);
insert into account (custno, type, balance) values (2,5,0.0);
insert into account (custno, type, balance) values (3,2,563.00);
insert into account (custno, type, balance) values (3,3,20000.00);
insert into account (custno, type, balance) values (3,6,1000000.00);
create table checking (acctno int, custno int, primary key (acctno, custno), index acpair(acctno,custno), foreign key (acctno,custno) references account(acctno, custno));
insert into checking (acctno, custno) values (1,1);
insert into checking (acctno, custno) values (6,2);
create table saving (acctno int, custno int, apr double, primary key (acctno, custno), index acpair(acctno,custno), foreign key (acctno,custno) references account(acctno,custno));
insert into saving (acctno, custno, apr) values (2,1,0.25);
insert into saving (acctno, custno, apr) values (7,2,0.015);
insert into saving (acctno, custno, apr) values (10,3,0.005);
create table cd (acctno int, custno int, apr double, mature date, primary key (acctno, custno), index acpair(acctno,custno), foreign key (acctno,custno) references account(acctno,custno));
insert into cd (acctno, custno, apr, mature) values (3,1,0.05,'2020-09-10');
insert into cd (acctno, custno, apr, mature) values (11,3,0.045,'2020-12-31');
create table visa (acctno int, custno int, minpay double, due date, rate double, primary key (acctno, custno), index acpair(acctno,custno), foreign key (acctno,custno) references account(acctno,custno));
insert into visa (acctno, custno, minpay, due, rate) values (4,1,25.00,'2020-09-19',0.15);
insert into visa (acctno, custno, minpay, due, rate) values (8,2,25.00,'2020-09-21',0.15);
create table debit (acctno int, custno int, primary key (acctno, custno), index acpair(acctno,custno), foreign key (acctno,custno) references account(acctno,custno));
insert into debit (acctno, custno) values (5,1);
insert into debit (acctno, custno) values (9,2);
create table home (acctno int, custno int, payment double, due date, rate double, term int, primary key (acctno, custno), index acpair(acctno,custno), foreign key (acctno,custno) references account(acctno,custno));
insert into home (acctno, custno, payment, due, rate, term) values (12,3,3696.19,'2020-09-30',0.02,30);
# It is a good idea to do all further work with the sample database as a regular user
# as opposed to the root user. To get up yourself as a user, invoke the mysql command
# as the root user then enter at the mysql prompt:
# mysql> create user <yourID>@localhost identified by 'securepasswd';
# check to make sure this worked with:
# mysql> select user, host from mysql.user;
# Then give yourID permissions to use the sample DB:
# mysql> grant all privileges on sample.* to 'yourID'@'localhost';
# To verify, log into mysql with the command:
# $ mysql -u yourID -p
# and enter the password. When logged in enter:
# mysql use sample;
# To verify enter:
# mysql> show tables;

Consider the following database schema:

               Product(maker, model, type)

               PC(model, speed, ram, hd, rd, price)

               Laptop(model, speed, ram, hd, screen, price)

               Printer(model, color, type, price)

Give SQL statement for each of the following:

2. (Subqueries, Join operations) Write the following queries in SQL:

1. Find the makers of PC’s with a speed of at least 1200.
2. Find the printers with the highest price.
3. Find the laptops whose speed is lower than that of any PC.
4. Find the model number of the item (PC, laptop, or printer) with the highest price.
5. Find the maker of the color printer with the lowest price.
6. Find the maker(s) of the PC(s) with the fastest processor among all those PC’s that have the smallest amount of RAM.

SQL query exercises:

Consider the following database schema:

               Product(maker, model, type)

               PC(model, speed, ram, hd, rd, price)

               Laptop(model, speed, ram, hd, screen, price)

               Printer(model, color, type, price)

Consider the Drivers-Cars-Reserves DB for a small rental car company:

               Drivers(id, name, rating, age)

               Cars(vin, maker, model, year, color)

               Reserves(did, vin, date)

1. Give SQL statement each of the following operations:

1. Find the colors of cars reserved by Smith.
2. Find all id’s of drivers who have a rating of at least 7 or reserved car with VIN:1HGCM82633A004352
3. Find the names of drivers who have not reserved a red car.
4. Find the names of drivers who have reserved at least two cars.
5. Find the driver id’s of all drivers whose rating is better than some driver called Bob.
6. Find the driver id’s of all drivers whose rating is better than every driver called Bob.
7. Find the driver id’s of all drivers with the highest rating.
8. Find the names of drivers who have reserved all cars.

Draw a complete ERD of a database design to meet their needs

First Urban Rescue (FUR ) takes in cats and dogs that are no longer wanted in their old home , and tries to find them new homes . For each pet taken in by FUR , the pet is given a name if it doesn't already have one . The breed , name , approximate age , height , weight , and date arrived at FUR is recorded for every pet that comes in . For each breed , FUR keeps the breed name , the normal size of that breed (" very small ", "small ", medium ", large ", or " very large ) , a general description of the breed , and whether it is a canine or feline breed . Every pet is associated with one and only one breed . FUR may have several pets of a given breed . It is possible that there is data about certain breeds for which FUR has never had a pet of that breed . One of the first things that FUR does with new pets is to ensure that they receive the proper vaccinations . For each vaccination available , the disease that it prevents , the type of vaccine , and a description of the vaccination are recorded . A pet will typically receive many vaccinations , although some pets don't need any . A vaccination can be given to several different pets , although some new vaccinations are occasionally developed that haven't been used on any pet yet . Each time a pet receives a vaccination , the date administered must be recorded . When a person adopts a pet , the person's name and address are recorded along with the adoption date of that pet . A person can adopt several pets . Unfortunately , adoptions do not always work out , so a pet can be returned . If a pet is returned , the date of return for that adoption must be recorded . Therefore , it is possible for a pet to be adopted more than once . Not all pets get adopted . Only people that have adopted a pet are recorded in the system

SUBJECT: DATABASE SYSTEMS

!!! Please answer the question as early as possible !!!

Question 1
You are required to draw a complete Crow’s Foot ERD that includes the following entity
relationship components based on the below descriptions:
i) Identify all of the entities and its attributes.
ii) Identify all possible relationships and its connectivity.
iii) Identify the primary key and foreign key for each entity.
iv) Identify the participation constraint and cardinality for each relationship.
A Super5 company contains many departments. Each department has name and is identified
by department code. Each department employs many employees. An employee can work only
in one department. Each employee has ID number, name, job specialization, and email as the
attribute. Not all employees such as president and vice presidents will be assigned a
department.
Manager within the employee may supervises at least fifteen employees under his/her
supervision. An employee can be supervised by only one manage but president and vice
presidents no need manager to supervise them.
Every year Super5 company will organized a fund-raising events to aid an orphanage home.
During the event, a department needs to set up at most three committees and each committee
handled by one and only one department. The committee has committee id and committee
name. But not all departments need to set up a committee.
Each committee will be assigned to do many tasks. A task will be completed by one and only
one committee. The maximum number of tasks per committee is five. Special task will not be
handled by committee. The task will have the task number, task name, assign date, and task
duration to be completed.
There are many employees’ becoming a member of the committee. Each employee can work
in a committee based on the fund-rising event. This event will be held from time to time basis.
Whenever an employee is participating as committee member in fund-raising event,
participation date, event name, and position must be stored. Each event will have a unique
code.

You are given the following schemas for a corporation database. The corporation owns several subsidiary companies (e.g. Disney Corporation owns Lucasfilm Ltd). Managers are also Employees. Answer the following questions using the schemas.

Employee(ssn, name, street, city)

Company(company_name, asset)

Company_Branches(company_name, branch_num, city)

Works(ssn, company_name, branch_num, salary)

Managed_By(ssn, manager_ssn)

6) Does the current design allow us to enforce the constraint that each employee must have a manager (without using external check or triggers)? Why or why not?

7) If the answer to the above is “no,” what changes do we need to make on the schema(s) to allow enforcement of such a constraint?