Description
CSCI 466 Assignment 1 ER Diagram – Fitness Tracker
The Goal
You are being employed by a company that offers a fitness tracking service. They are working on a phone app
that will allow the user to track what they eat, as well as when/how they work out. Another employee will be
designing the user interface, but you are responsible for designing the database. Design an ER diagram
to fulfill this goal, making sure to meet all of the requirements. All entities must have an appropriate
identifier specified. If a surrogate key is used, explain why a natural key was not appropriate. In the interests
of saving space, attributes that are not part of an identifier may be omitted from the diagram, but they should
be included and explained in that portion of your submission. (See “What to turn in?” below.)
Requirements
• Every user will have an account, and all of their meals and workouts will be linked to this account.
• To track weight loss, the user will update their weight periodically. This data must be retained.
• The serving size will be some number of units (grams, ounces, Tbsp, cups, lbs, etc.). There will be
information stored for conversion between different unit types.
• There will need to be a database of foods/beverages. Each of these will have information on serving size,
calories per serving, and grams per serving of each of the macronutrients (protein, fat, carbohydrates).
• It should be possible to store information on the quantities of micronutrients or chemicals (i.e. vitamin
D, caffeine) that are present in a given food or beverage in significant amounts. Recommended daily
values for any of these should be stored, when applicable.
• Each time the user eats, a record of who ate how many servings of what and when is stored.
• The app needs to allow a user to track their workouts. This includes the type of the workout, its
intensity, and its duration.
• When a user tracks their workout, a record is created of who did what type of workout, when, and for
how long.
Note: If you are not sure what needs to be included for foods/drinks/etc., a good place to look for example
data would be the nutritional facts on food packaging.
Necessary Views
The database needs to be able to store its operational data in such a way that the final app will be able to show
the following views (at minimum). You don’t need to implement the views, but the necessary data should be
modeled.
• A graph will be generated of how many calories a user consumed each day of the week.
• A similar graph will be generated that shows how many calories were burnt each day of the week
through workout.
• Show a pie graph of the percentage of the diet made up of each macronutrient during a given time
period (day, week, month).
• Track the consumption of a given micronutrient/chemical over a specified period of time. If there is a
recommended daily value, show a comparison of their consumption with the recommended amount.
• Allow the user to search through the food database to find common foods, in order to plan their diets.
These same foods will be used to track their eating.
• The user will search through a list of workouts to find the one closest to the one they’re going to do,
in order to track the activity.
• A line graph of user weight over time.
CSCI 466 Assignment 1 2 of 2
What to turn in?
You should turn in, via Blackboard, the following:
• A PDF file containing the diagram you’ve drawn. You can draw this in any graphics program. If you
must, you may draw it by hand and scan it, but it must be legible. Credit will not be given for portions
of the diagram that cannot be read.
• A text file or another PDF that explains all entities and relationships you’ve chosen to use, along with
a description of each of their attributes. List any assumptions that you will have made when deciding
cardinalities that are not specified explicitly by the requirements.
CSCI 466 Assignment 2 Normalization
The Task
We discussed in class that a relational database designed in a poor way will allow for anomalies to occur. This is
undesirable, so we use normalization to prevent them. Several relations are provided below, along with their
functional dependencies. Answer the questions provided and fix what is broken. Perform only the current
step for each question, i.e. when fixing 1NF, fix only 1NF, leaving the 2NF and 3NF violations alone until the
question that asks about them.
The Questions
For each of the below, part (b) refers to the results of part (a), and part (c) refers to the results of part (b) –
any changes made during the previous steps should be considered in the steps that follow. Each question is
worth 12 points.
1. Pharmacy(patient_id, patient_name, address, (Rx_num, trademark_name, generic_name, (filldate,
num_refills_left), num_refills))
Functional Dependencies:
• patient_id ⟶ patient_name, address
• patient_id, Rx_num ⟶ trademark_name, generic_name
• Rx_num ⟶ num_refills
• Rx_num, filldate ⟶ num_refills_left
a) Is this relation in 1NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
b) Is this relation in 2NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
c) Is this relation in 3NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
2. Company(EmpID, EmpName, EmpAddr, (ProjID, ProjName, MgrID, MgrName, HoursWorked))
Functional Dependencies:
• EmpID ⟶ EmpName, EmpAddr
• ProjID ⟶ ProjName, MgrID, MgrName
• EmpID, ProjID ⟶ HoursWorked
• MgrID ⟶ MgrName
a) Is this relation in 1NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
b) Is this relation in 2NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
c) Is this relation in 3NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
CSCI 466 Assignment 2 2 of 2
3. Property(id, county, lotNum, lotArea, price, taxRate, (datePaid, amount))
Functional Dependencies:
• id ⟶ county, lotNum, lotArea, price, taxRate
• lotArea ⟶ price
• county ⟶ taxRate
• id, datePaid ⟶ amount
a) Is this relation in 1NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
b) Is this relation in 2NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
c) Is this relation in 3NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
4. StockExchange(Company, Symbol, HQ, Date, ClosePrice)
Functional Dependencies:
• Symbol, Date ⟶ Company, HQ, ClosePrice
• Symbol ⟶ Company, HQ
• Symbol ⟶ HQ
a) Is this relation in 1NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
b) Is this relation in 2NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
c) Is this relation in 3NF? If not, write an explanation of why it isn’t, then make the necessary changes
to fix it.
What to turn in?
Answers to the questions should be submitted via Blackboard as a PDF with your name and section at the
top of each page.
CSCI 466 Assignment 3 ER Diagram to Third Normal Form
Introduction
For this assignment, you will be converting the following ER diagram, which models the operational data for
a department store, to a set of relations that conform to Third Normal Form (3NF). Use the steps from the
slides that we went over in class. Make sure to indicate the primary keys by underlining them. Include a list
of which attributes are foreign keys, with the home relation of each foreign key indicated.
ER Diagram
Store
Store_ID
Address
Manager
City
City_Name
State
HeadQuartersAddr
Item
Item_ID
Description
Size
Color
Customer
Customer_Name
Address
Phone_Number
Order
Order_Num
Date
located in
(1,n)
(1,1)
stored in
(1,n)
(1,n)
Quantity
ordered in
(1,n)
(1,n)
Quantity
placed (1,1)
(1,n)
hold
(1,n)
(1,n)
Quantity
Figure 1: This is the ER diagram. There may be some things that may seem weird. These may be errors
that were missed during the design phase, or they could be intentional. The purpose of this assignment is to
convert it, not to revise it.
What to turn in?
Submit a PDF file through Blackboard containing the relational schema for the database, including the
information required above.
CSCI 466 Assignment 4 ER Diagram, Normalization, DDL
Introduction
For this assignment, you will be making another ER diagram modeling the operational data of the enterprise
described below. All entities, relationships, and attributes must be labeled, with cardinalities shown, and
identifiers marked appropriately. This time, you will also be converting the ER diagram into relations that
can be used to store the data. You will also supply SQL to add these relations to your database.
Description
Serenity Springs Day Spa is a full-service spa that provides hair, makeup, manicures, massages, and waxes.
They would like to update their filing sytem, which has been entirely paper-based up until now. They would
like to be able to automatically bill clients, remind clients of upcoming appointments, print lists of employees,
print employees’ work schedules, and list the clients scheduled for a particular day.
They need to store basic information like name, phone number, physical address and email for both clients
and employees. Employees have a job title, a specialty, and a pay rate that also need to be stored. Clients can
choose a preferred employee for each of the services offered by the spa.
Although each employee has a specialty, they should be able to perform any of the services offered by the
spa. The spa would like to be able to automatically generate a complete list of services and their prices from
their new database.
What to turn in?
Submit, through Blackboard, the following:
• The ER diagram that you have designed. This should be in a common image format, or a PDF. It may
be hand drawn, but must be legible for credit to be awarded.
• A second page like the one from the first ER diagram assignment, with all of the entities, relationships
and attributes from the ER diagram listed and described.
• The relational schema resulting from your ER diagram, in the format discussed in class. Primary keys
need to be underlined, and foreign keys identified in a clear way.
• A text file, dayspa.sql with valid SQL CREATE TABLE statements to create each of the relations for your
database. Do not forget the foreign key constraints.
CSCI 466 Assignment 5 SQL – DDL
Introduction
For this assignment, you must write an SQL script. An SQL script is a text file that contains a sequence of
SQL commands to be run. Yours should include commands for each of the following, in order:
1. Run a statement that will remove all of the tables that will be created below. This will allow the script
to be run again without any errors caused by existing tables.
2. Create a table called dogs with a dog id, a breed, and a name. The id of the dog should be the primary
key, and should be automatically assigned the next available value when inserting a new row into the
table.
3. Put five rows into the dog table with example data. Make up the data yourself.
4. Run the command DESCRIBE dogs;
5. Run the command SELECT * FROM dogs;
6. Create a table called visits that contains a visit id as primary key (this should take the next available
key value when a new row is added). It should also have a foreign key that references a row in the dog
table, and an attribute to hold the date that the visit took place.
7. Put at least eight new rows into the visits table. Since there are only five dogs, this means that some
dogs will have multiple visits.
8. Run the command DESCRIBE visits;
9. Run the command SELECT * FROM visits;
10. Add a column to the visits table to hold the time of the visit.
11. Change the value for the newly-added attribute in several of the existing rows. There are several
date/time functions available in SQL, and you can choose to use any that are appropriate.
12. Run the command SELECT * FROM visits; again.
If a data type is not specified in this document, anything reasonable should be fine. Use your discretion.
You should include comments in the script that identify which commands match which of the requirements.
Comments in SQL are written with a — before them.
What to turn in?
Submit, through Blackboard, the following:
1. A text file containing the script you wrote that accomplishes the above. It should be suitable for
running with the \. command in the SQL client.
2. Another text file containing the output generated after running the script your wrote. You can use \T
filename to start logging to a file called filename, and \t to stop logging when you are done. It is
also possible, though not recommended, to get the output by copying and pasting from the terminal.
Make sure to choose names for these two files that make it easy for the TA to determine which is which
without having to open the file.
1
CSCI 466 Assignment 6 SQL – DML – Single Table
Introduction
You will be writing another SQL script. The script should work with the BabyName database, so it will need
the appropriate USE statement. Test each of the SQL statements required for the below, and then put them
into the script, making sure to comment each one. Include your name, zid, and section in each of the files
you submit.
This is a fairly large database, so it is recommended that you add LIMIT 50 to your SQL statements when
testing, to prevent a nigh-neverending cascade of text from needing to be downloaded.
Your script must do each of the following:
1. Select the BabyName database.
2. Show all of the tables in the database.
3. Show all of the columns and their types for each table in the database.
4. Show all of the years (once only) where your first name appears. Some people’s names may not be
present in the database. If your name is one of those, then use ‘Chad’ if you are male, or ‘Stacy’ if you
are female. If you don’t feel you fit into one of those, feel free to use ‘Pat’.
5. Show all of the names from your birth year. (Showing each name only once.)
6. Display the most popular male and female names from the year of your birth.
7. Show all the information available about names similar to your name (or the one you adopted from
above), sorted in alphabetical order by name, then, within that, by count, and finally, by the year.
8. Show how many rows there are in the table.
9. Show how many names there were in the year 1972.
10. Show how many names are in the table for each sex from the year 1953.
11. List how many different names there are for each place.
What to turn in?
Submit, through Blackboard, the following:
1. The SQL script that accomplishes the above task.
2. The output yielded by having MariaDB run the script, in a text file.
CSCI 466 Assignment 7 HTML Form
What to do?
For this assignment, you will be constructing an HTML file containing well-formed HTML that satisfies the
requirements below.
Requirements
1. The title of the page should have your name and zid in it. (10pts)
2. The page should have a heading at the top that says “CSCI 466 Assignment 7” (10pts)
3. There must be an image on your page, and that image must be part of a link to another site. (10pts)
4. There must be three paragraphs worth of text. Have each in its own paragraph element. Be creative.
(10pts)
5. An ordered list of your top ten favorite foods must be shown. (10pts)
6. Include an HTML table element that shows a 5 × 5 multiplication table, with rows and columsn for the
numbers 1, 3, 5, 7 , 9. (10pts)
7. There must be a form element meeting the following requirements: (5pts each × 8 items = 40pts)
a. The form must submit to https://students.cs.niu.edu/~z035690/asnsubmit.php.
b. The values submitted by the form must not appear in the URL.
c. There must be a single line text box whose value is associated with the name “banana”.
d. There must be a series of three checkboxes. They should be labeled “Carbohydrates”, “Fat”, and
“Protein”, and submit those values under the name “macros”.
e. There must be a group of three radio buttons labeled “A”, “B”, and “C”. They should submit a
value that matches their label and the server should be able to access it under the name “abc”.
f. The “A” radio button should start off in a checked state.
g. There must be a field in your form that is not displayed in the browser, but which contains your
zid as a value and submits it under the name “zid”.
h. The submit button must have a caption of “Make it so”.
What to turn in?
Submit, through Blackboard, the following:
• The HTML file. Name it z1234567.html but with your zid instead. Blackboard will not accept
HTML files, so you will have to compress the .html into a .zip to submit.
• A link to that same HTML file, hosted on our webserver. Place it in your public_html directory and
provide the appropriate link in the comments of your submission.
DO NOT DELETE THE HTML FILE FROM THE SERVER UNTIL AFTER YOUR ASSIGNMENT HAS BEEN GRADED. IF IT IS MISSING WHEN THE TA CHECKS FOR IT, YOU
WILL RECEIVE A ZERO.
CSCI 466 Assignment 8 PHP w/ Forms – Parts/Suppliers
Introduction
For this assignment, you will be developing PHP/PDO code for a page or set of pages that fulfill(s) the
requirements found below.
Using your personal database on the MariaDB server (the one that has your zid as its name), create the tables
from the SQL SELECT slides (S, SP, P) and make sure they contain the data in the rows shown in the slides
(before adding the extra rows later). None of the columns should be allowed to be NULL. Data types are not
explicitly specified in the tables from the slides, so feel free to choose any that would work to store the data
of the types shown.
Requirements
1) There must be a way to show all of the suppliers. (10pts)
2) There must be a way to show all of the parts. (10pts)
3) There must be a form that allows a user to select a part and see everyone who supplies that part, along
with how many of that part they have available. The information on the part chosen should be shown
as well. (20pts)
4) There must be a form that allows a user to select a supplier and get a list of all of the parts they supply
and how many of each they have. Information on the supplier chosen should also be displayed. (20pts)
5) There must be a form that allows a user to add a new part to the database. (20pts)
6) There must be a form that allows a user to add a new supplier to the database. (20pts)
Important Notes
• You will have a fair amount of leeway on the design of the page. The functionality is the most important
part, but it should be obvious how your pages work while the TA’s are grading. The portions that fulfill
the requirements should stand out.
• You are required to use prepared statements for any queries that will contain data that the user submits.
• Note: Do not forget that PHP code only runs ON THE SERVER when the page is requested. Additionally, the files containing your code must have an extension of .php or the code will not be run.
What to turn in?
Submit, through Blackboard, the following:
• The PHP file(s) that implement your pages. If you have more than one, name them in a way that makes
the purpose of each file clear. (Their names should match the ones that make it work on the server,
whose names must follow this requirement)
• A link to that same PHP file, hosted on the NIU webserver. Place it in the appropriate place in your
public_html directory and provide the URL to it in the comments of the Blackboard submission page.
IMPORTANT: DO NOT DELETE THE PHP FILES FROM THE SERVER UNTIL AFTER
YOUR ASSIGNMENT HAS BEEN GRADED. IF ANY OF THEM ARE MISSING WHEN THE
TA CHECKS FOR THEM, YOU WILL RECEIVE A ZERO.
CSCI 466 Assignment 9 Transactions in MariaDB
Purpose
The purpose of this assignment is to experiment with the behavior of transactions using a MariaDB database.
You will need to use more than one MariaDB session for these to work. To do this, just open up two separate
putty sessions to turing or hopper and log into MariaDB on each of them. Use the same name for the output
file for all of the sessions – \T assign9out.txt – so that the output from all of your sessions ends up in the
same file. When znnnnnnn is used, replace it with your own z-id.
Part I – The Power of COMMIT (25pts)
1) Start your first MariaDB seesion, issue the following SQL queries:
\T assign9out.txt
USE znnnnnnn;
CREATE TABLE Fall(
pk INT PRIMARY KEY,
data CHAR(15));
START TRANSACTION;
INSERT INTO Fall
VALUES(1, ‘dataA’);
INSERT INTO Fall
VALUES(2, ‘dataB’);
INSERT INTO Fall
VALUES(3, ‘dataC’);
2) Start your second MariaDB session, and run the following SQL queries in it.
\T assign9out.txt
USE znnnnnnn;
SELECT * from Fall;
Question 1.2) What is the result of running the SELECT statement. Why?
3) In that second session, run the following:
INSERT INTO FalI VALUES(4, ‘dataD’);
INSERT INTO Fall VALUES(5, ‘dataE’);
4) Switching back to the first MariaDB session, issue the following queries:
COMMIT;
SELECT * FROM Fall;
\t
exit;
5) Switch back to the second MariaDB instance, and run the following queries:
SELECT * FROM Fall;
\t
exit;
Question 1.5) What is the result of the SELECT statement above?
CSCI 466 Assignment 9 2 of 3
Part II — The Power of ROLLBACK (25pts)
1) Start another MariaDB session, issue following MariaDB statements:
\T assign9out.txt
USE znnnnnnn;
START TRANSACTION;
DELETE FROM Fall WHERE pk = 3;
SELECT * FROM fall;
2) Then
UPDATE Fall
SET Data = ‘changed’
WHERE pk = 2;
3) Then
UPDATE Fall
SET Data = ‘changed 2’
WHERE pk = 4;
4) Then
INSERT INTO Fall VALUES(6, ‘dataF’);
SELECT * FROM Spring;
Question 2.4) What is the result of the SELECT statement, and why?
5) Issue the following MariaDB statements:
ROLLBACK;
SELECT * FROM Fall;
Question 2.5) What is the result of the SELECT statement, and why?
\t
exit;
Part III: Be Aware of Deadlock (25pts)
Using another two sessions of MariaDB, do the following in the order specified:
1) In session 1,
\T assign9out.txt
USE znnnnnn;
START TRANSACTION;
2) In session 2,
\T assign9out.txt
USE znnnnnn;
START TRANSACTION;
3) In session 1,
CSCI 466 Assignment 9 3 of 3
UPDATE Fall
SET data = ‘data1A’
WHERE pk=1;
4) In session 2,
UPDATE Fall
SET data= ‘data2B’
WHERE pk = 2;
5) In session 1,
UPDATE Fall
SET data = ‘data5E’
WHERE pk = 5;
6) In session 2,
UPDATE Fall
SET DAta = ‘data12B’
WHERE pk = 1;
Question 3) What happened here?
Notes
There may be typos in these statements. If there is a syntax error, fix it. These errors were made to encourage
you to pay close attention, and to type the statements in yourself as practice.
What to turn in?
Turn in, via Blackboard, the following:
• The output generated by running the commands in both of the sessions.
• The answers to the questions asked, (1.2, 1.5, 2.4, 2.5, and 3), in a separate text file.
CSCI 466 Assignment 10 MariaDB in C++
The Task
For this assignment, you will be writing a C/C++ program that prints reports on the books found in the
henrybooks database from our MariaDB server.
Requirements
Your program will show a menu with three options for reports to show. The user will choose an option,
and your program will use the MariaDB C API to run the necessary queries, displaying the results, neatly
formatted, in the terminal.
1. Book List: For each book, print the title, the author(s), and the cost. Do not assume that a book will
be written by a single author. Use a subquery to accomplish this. Sort the authors for each book based
on their Sequence.
2. Author Search: Prompt the user for the name of an Author. Print a report showing all books by
authors whose first or last names match the user-supplied name. For each of the books found, show a
line in your output with the book code, the title, the name of the author that matched your search, and
the price; then show individual lines (indented below the original line) for each branch, each showing
how many of the current book are on hand, or a single line indicating that it is out of stock everywhere if
no branches have any on hand. Branches should be displayed as their names and not as their numerical
identifiers, in alphabetical order.
3. Title Search: Same as the author search in feature 2, but search by title instead of by author. For the
author field, use the first author only (by sequence number).
There should also be an option to quit. After performing whichever option is chosen (obviously except for
the one to quit), your program should show the menu again and allow the user to choose a new option to
run.
Notes
• The grading for this will be done by compiling and running this program on turing and/or hopper. If
your program does not compile and run properly on turing and hopper, you will not receive credit, so
make sure to test it there.
• If your program needs any flags other than the ones used in the examples of gcc/g++ in the slides in
order to compile, you need to let us know when you submit.
• Don’t forget to document your code.
• It is not a requirement that every feature be implemented with a single query. You can break the tasks
down into smaller queries if you find it makes solving the problem easier.
• As always, you should be submitting your own work. Do not try to cheat or plagiarize other people’s
programs.
What to turn in?
Submit, through Blackboard, the following:
• Your C++ source code, in a file called books.cc.
• Any other source code or header files your program needs in order to compile.
CSCI 466 Assignment 10 2 of 2
Schema for henrybooks
For your convenience, I have included information on the schema of henrybooks below:
Table Field Type Null Key Default Extra
Author AuthorNum decimal(2,0) NO PRI NULL
AuthorLast char(12) YES NULL
AuthorFirst char(10) YES NULL
Book BookCode char(4) NO PRI NULL
Title char(40) YES NULL
PublisherCode char(3) YES NULL
Type char(3) YES NULL
Price decimal(8,2) YES NULL
Paperback char(1) YES NULL
Branch BranchNum decimal(2,0) NO PRI NULL
BranchName char(50) YES NULL
BranchLocation char(50) YES NULL
NumEmployees decimal(2,0) YES NULL
Inventory BookCode char(4) NO PRI
BranchNum decimal(2,0) NO PRI 0
OnHand decimal(2,0) YES NULL
Publisher PublisherCode char(3) NO PRI NULL
PublisherName char(25) YES NULL
City char(20) YES NULL
Wrote BookCode char(4) NO PRI
AuthorNum decimal(2,0) NO PRI 0
Sequence decimal(2,0) YES NULL
