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?

I'm using livesql for my database class to try and do these

A medical clinic has doctors, along with patients assigned to one of the doctors.

Create 2 tables that include fields below with primary key chosen below. Use appropriate data types and simplified field names, eg: -

Doctor’s Table:
DKey ID          
Last name
First name
Home phone
Medical specialty
Monthly Salary

Patient’s Table:
PKey ID          
Last name
First name
Address (Use Address as column name)
Home Phone  
Age (in years)      
DoctorID Assigned
Medical History
Add 3 doctors and about 8 patients. Values will be provided in a text file to save time.

Create 3 select statements
List all doctors,
List all patients,
Using where clause, show which doctors have which patients. (Make sure your result indicates who is Doctor and who is Patient.

List the ID num, first and last name of doctors who has a salary that is greater than 16000.

Create the following aggregate select statements.
The monthly sum of all doctors’ salaries
The average age of the patients

Write a SQL statement to show the total number of rows in Patient table. Sort by last name.

Select the lastname, firstname, assigned doctor of patients where the patient’s medical history is Diabetic.

Write a SQL statement to change one of the patient’s name “Kasuma” who recently got married and which to use her married name “Chutaw.” Patient ID number is 103. (10pts)

Write a SQL statement to increase all Doctors salary by 20% and rename column as Salary Increase

List all the patients with high blood pressure and are older than 49 years old.

text file with values are:

Doctor Table


('AM01','Abrams','Miles','617-555-6032','MD', 20000);

('BR01','Boyers','Rita','603-555-2134','MD', 15000);

('DH01','Devon','Harley','781-555-7767','MD', 17000);



Patient Table


('101','Northfold','Liam','9 Old Mill Rd. Londonderry NH','603-555-7563', '45', 'BR01', 'Breat Cancer');

('102','Ocean','Arnold','2332 South St. Apt 3 Springfield MA','413-555-3212', '55','AM01', 'Diabetic');

('103','Kasuma','Sujata','132 Main St. 1 East Hartford CT','860-555-0703','64', 'DH01', 'High Blood Pressure');

('104','Goff','Ryan','164A South Bend Rd.Lowell MA','781-555-8423','50','DH01','Left Renal abnormality');

('105','McLean','Kyle','345 Lower Ave. Wolcott NY','585-555-5321', '60','BR01', 'Diabetic');

('106','Morontoia','Joseph','156 Scholar St. Johnston RI','401-555-4848', '52','DH01','High Blood Pressure');

('107','Marchand','Quinn','76 Cross Rd. Bath NH','603-555-0456', '40','AM01','Diabetic');

('108','Rulf','Uschi','32 Sheep Stop St. Edinboro PA','814-555-5521', '75','BR01', 'Low Blood Count');

i'm stuck at creating a second table "create table patient" and livesql keeps telling me im missing a parenthesis >_<

1. Write a Java program that implements a song database. The SongsDatabase class keeps tracks of song titles by classifying them according to genre (e.g., Pop, Rock, etc.). The class uses a HashMap to map a genre with a set of songs that belong to such a genre. The set of songs will be represented using a HashSet. Your driver output should sufficiently prove that your code properly implements the code below.

public class SongsDatabase {

private Map<String, Set<String>> genreMap =

new HashMap<String, Set<String>>();

    public void addSong(String genre, String songTitle) {

        // You must implement this method

    }

    public Set<String> getSongs(String genre) {

        // You must implement this method

    }

    public String getGenreOfSong(String songTitle) {

        // You must implement this method

    }

}

So, your hashMap, visually, looks like the following:

Soul -> (SoulSong1, SoulSong2, SoulSong3, etc.)

Rock -> (RockSong1, RockSong2, RockSong3, etc.)

Jazz -> (JazzSong1, JazzSong2, JazzSong3, etc.)

PHP, JAVASCRIPT, MYSQL below is what I have. Can fill in the details for the database

5. “Login to DB”, “Logout DB”, sub-menus of “File”
5.1 _____ When the user selects “Login to DB”, a window should popup asking the user to enter
login and password. Your program should verify the login and password against the DV_User table in
the datamining database. A corresponding message should be shown in the message area when the
login failed or successful. If it is successful, please store the user information on the browser and the
message should include welcome and the user name.
5.2 _____ When the user selects “Logout DB”, a window should popup asking the user to confirm.
If user confirms to logout, your program should clean the user information from the browser, and a
“Successful logout” should be displayed in the message area.

index.html

<!DOCTYPE html>

<html>

<head>

<meta name="viewport" content="width=device-width, initial-scale=1">

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>

<link rel="stylesheet" href="style.css">

</head>

<body>

<div class="navbar">

<div class="subnav">

<button class="subnavbtn">File <i class="fa fa-caret-down"></i></button>

<div class="subnav-content">

<input id="csvfile" type="file" style="display:none" />

<a href="#" id="loadCSV">Load CSV file</a>

<a href="#" id='navLoginDB' onclick="return loginDB('show');">Login to DB</a>

<a href="#" id='navLogoutDB' onclick="return logoutDB();">Logout DB</a>

<a href="#">Exit</a>

</div>

</div>

<div class="subnav">

<button class="subnavbtn">View <i class="fa fa-caret-down"></i></button>

<div class="subnav-content">

<a href="#">Line</a>

<a href="#">Pie</a>

<a href="#">Bar</a>

<a href="#">Map</a>

</div>

</div>

<div class="subnav">

<button class="subnavbtn">Setting <i class="fa fa-caret-down"></i></button>

<div class="subnav-content">

<a href="#link1">User Info</a>

</div>

</div>

<div class="subnav">

<button class="subnavbtn">Help <i class="fa fa-caret-down"></i></button>

<div class="subnav-content">

<a href="#">Info</a>

<a href="#">Client</a>

</div>

</div>

</div>

<div id="dvCSV">

</div>

<script>

$(document).ready(function () {

var browserSupportFileUpload = function () {

var isCompatible = false;

if (window.File && window.FileReader && window.FileList && window.Blob) {

isCompatible = true;

}

return isCompatible;

};

if (browserSupportFileUpload()) {

document.getElementById('csvfile').addEventListener('change', uploadFile, false);

} else {

$("#message").html('The File APIs is not fully supported in thie browser. please use another browser.');

}

$(function () {

$("#loadCSV").click(function(e){

//$("#loadCSV").on('click', function (e) {

e.preventDefault();

$("#csvfile:hidden").trigger('click');

});

});

var uploadFile = function (event) {

var file = event.target.files[0];

var fileformat;

if (file.name.indexOf("csv") != -1) {

fileformat = true;

}

else {

fileformat = false;

}

var reader = new FileReader();

reader.readAsText(file);

reader.onload = function (event) {

if ((file.size / Math.pow(1024, 3)) > navigator.deviceMemory) {

document.getElementById("message").innerHTML = "The data requires more memory than the client can offer";

}

else if (!fileformat) {

document.getElementById("message").innerHTML = "The data is in wrong format. Only CSV file can be loaded!";

}

else {

createArray($.csv.toArrays(event.target.result));

}

};

};

function createArray(data) {

if (data !== null && data !== "" && data.length > 1) {

this.CSVdata = data;

document.getElementById("message").innerHTML = " File upload successful with" + ((CSVdata.length) - 1) + " records! lower threshold is:" + highlight('OutlierL') + " upper threshold is: " + highlight('OutlierU');

var wage = findAvg('Avg');

var pop = findAvg('Pop');

if (getCookie("p1UserName") != "") {

var uname = getCookie("p1UserName");

$.ajax({

url: "",

type: 'POST',

async: false,

data: {

uname: uname

},

success: function (response) {

var dd = JSON.parse(response);

console.log(dd);

wage = parseFloat(dd[0][0]);

pop = parseFloat(dd[0][1]);

}

});

}

}

}

}

);

</script>

</body>

</html>

style.css

body {

font-family: Arial, Helvetica, sans-serif;

margin: 0;

}

.navbar {

overflow: hidden;

background-color: #333;

}

.navbar a {

float: left;

font-size: 16px;

color: white;

text-align: center;

padding: 14px 16px;

text-decoration: none;

}

.subnav {

float: left;

overflow: hidden;

}

.subnav .subnavbtn {

font-size: 16px;

border: none;

outline: none;

color: white;

padding: 14px 16px;

background-color: inherit;

font-family: inherit;

margin: 0;

}

.navbar a:hover, .subnav:hover .subnavbtn {

background-color: red;

}

.subnav-content {

display: none;

position: absolute;

left: 0;

background-color: red;

width: 100%;

z-index: 1;

}

.subnav-content a {

float: left;

color: white;

text-decoration: none;

}

.subnav-content a:hover {

background-color: #eee;

color: black;

}

.subnav:hover .subnav-content {

display: block;

}

#upload {

display: block;

visibility: hidden;

width: 0;

height: 0;

}

table {

border-collapse: collapse;

}

table, th, td {

border: 1px solid black;

}

Scenario: A builder needs a database to keep track of contractors he hires for various projects. So far, we have this 2NF relation, with sample data shown. Normalize to 3NF.

CONTRACTOR: # ConID, Lname, Fname, JobTitle, Company, Street, City, State, Zip, CompanyPhone, CellPhone

ConID	Lname	Fname	JobTitle	Company	Street	City	State	Zip	Phone	CellPhone
2	Garcia	Mary	Carpenter	Construct Co	123 Main	Portland	OR	97204	823-1234	645-5423
14	Jones	Tomas	Welder	Construct Co	123 Main	Portland	OR	97204	823-1234	344-3475
21	Smith	Joey	Welder	Construct Co	123 Main	Portland	OR	97204	823-1234	676-0495
8	Tanaka	Bill	Plumber	Water Inc	456 21st St	Portland	OR	97219	722-0045	805-7602
9	Alredd	Pete	PipeFitter	Water Inc	456 21st St	Portland	OR	97219	722-0045	341-1243

Explain your reasoning here (not what you did but why you did it). What does 3NF mean? How did you apply it?

Your 3NF relation or relations go here.