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 >_<
In: Computer Science
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.)
In: Computer Science
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;
}
In: Computer Science
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.
In: Computer Science
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.
In: Computer Science
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.
In: Computer Science
PHP
The database should have at least two tables with, at minimum, the following fields:
|
Table customers: Fields: |
Table billing: Fields: |
|
customer_ID |
customer_ID |
|
customer_L_Name |
customer_L_Name |
|
customer_F_Name |
service |
|
customer_Title (Mr, Ms, Dr. etc,) |
customer_bill |
|
street_Address |
amt_paid |
|
city_State_Zip |
bill_date |
|
customer_Phone |
date_paid |
|
customer_Email |
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.
In: Computer Science
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.
In: Computer Science
DATABASE MANAGEMENT SYSTEMS
After looking at the material in Section 2, go ahead and answer the following questions:
(Make sure you understand the difference between the two)
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)
|
Players |
Teams |
Coaches |
Soccer Balls |
To be defined |
|
Player ID |
Team ID |
Coach ID |
Serial Number |
….. |
|
First Name |
Team Name |
C_FirstName |
Maker |
….. |
|
Last Name |
City |
C_LastName |
Color |
….. |
|
Address1 |
Phone |
|||
|
City |
||||
|
State |
||||
|
Zip Code |
||||
|
Country of Origin |
In: Computer Science
Consider the database of a car rental company that contains three tables drivers, cars and reservation tables.
Drivers: Reservation: Cars:
|
Dno |
Dname |
age |
Dno |
Cno |
Day |
Cno |
Cmake |
Color |
||
|
22 |
Dustin |
45 |
22 |
101 |
10/10 |
101 |
BMW |
Blue |
||
|
29 |
Brutus |
33 |
22 |
102 |
10/10 |
102 |
VW |
Red |
||
|
31 |
Lubber |
55 |
22 |
103 |
10/8 |
103 |
OPEL |
Green |
||
|
32 |
Andy |
25 |
22 |
104 |
10/7 |
104 |
FIAT |
Red |
||
|
58 |
Rusty |
35 |
31 |
102 |
11/10 |
|||||
|
64 |
Horatio |
35 |
31 |
103 |
11/6 |
|||||
|
71 |
Zorba |
16 |
31 |
104 |
11/12 |
|||||
|
74 |
Horatio |
35 |
64 |
101 |
9/5 |
|||||
|
85 |
Art |
25 |
64 |
102 |
9/8 |
|||||
|
95 |
Bob |
63 |
74 |
103 |
9/8 |
|||||
|
23 |
Alice |
15 |
23 |
104 |
9/11 |
Drivers(Dno, Dname, age)
Reservation(Dno, Cno, Day)
Cars(Cno, Cmake, Color)
Where:
In: Computer Science