Database and Information Systems
Database代写/数据库作业代写/sql代写: 这是一个典型的数据库作业，包括了ER图，数据库设计,3NF设计等相关的内容，是一个综合性的作业
This first part of this homework is about relational database design, from ER diagrams to
relational schemas and their normalization. You should read the assigned readings from
Chapters 2, 3 and 9 (see syllabus), and submit answers to each question individually as .pdf
files using Canvas (under hw2) using file names hw2-1.pdf, hw2-2.pdf, …,hw2-7.pdf. Part 3 is a
programming question on embedded SQL in Node.js, and must be submitted as instructed at
the end of part 3.
Part 1: ER Diagrams and UML (35 points)
Question 1 (20 points):
a) Construct a UML diagram to depict the below entity relations using the notation that you
learned in class:
● A television series has a name, a network, and a production company. A series is
identified by the name and network.
● A television series has one or more seasons, which are identified by the number of the
season. A season also has the number of episodes and the year the season starts. No
season can exist without a corresponding series.
● A season has one or more episodes, identified by episode number, which is unique
within the season. Each episode also has a title and a length. No episode can exist
without a corresponding season.
● An actor is identified by name and birth date, and also has a nationality.
● A writer is also identified by name and birth date, and has a talent agency that
represents him or her.
● An actor can appear as a regular on a television series or a guest star on an episode.
● An episode has one or more writers. A writer writes one or more episodes.
Now modify your UML diagram to make the below 2 changes. You may show the entire new
diagram, or just the parts of it that have changed. If you choose to show the entire diagram,
please circle in red the parts that have changed.
b) An actor plays a particular character in a television series or episode.
c) An episode can be the pilot episode for a series. A series can have at most one pilot
episode
Question 2 (15 points):
Translate the following ER diagram to the relational model by specifying the resulting relations,
their attributes, keys and foreign keys, using SQL DDL.
Part 2: Functional Dependency and Normalization (35 Points)
Question 3 (8 points):
Consider a relation R = ABCDE. You are given the following dependencies:
A → BC
DE → A
C → D
(a) List all the keys for R (make sure they are minimal, i.e. not a superset of some other key).
(b) Is R in BCNF, 3NF, 2NF or 1NF? Justify why R is or isn’t in each of these normal for
Question 4 (6 points):
Give a minimal cover for the following set of dependencies:
A → B
ABC → D
DE → FG
ACE → DF
Question 5 (8 points):
Consider the relation R(ABCDEF) and the functional dependencies:
A→ BC
D → AF
Are the following decompositions lossless? Justify your answer.
(1) R1(ABCD) , R2(DEF)
(2) R1(ABCD) , R2(AEF)
Question 6 (5 points):
Consider the relation R(MNOPQ) and the functional dependencies:
MN → P
O → Q
Q → O
O → M
M → O
Is the decomposition of R into R1(MNO) and R2(MNPQ) dependency preserving? Justify your
answer.
Question 7 (8 points):
Consider the relation R(ABCDEFGH) and the following functional dependencies:
ABCD → E
A → G
B → H
E → F
(a) Find the key for this relation. Show your answer.
(b) Give BCNF decomposition of relation
Part 3: Embedded SQL in NodeJS (40 points)
Node.js is an extremely powerful JavaScript runtime built for executing JavaScript on the server
side. With this assignment, you will first set up NodeJS. Second, you will download two
datasets: NYC bikes and subways datasets. Third, you will develop a NodeJS app that displays
these datasets in your browser.
Installing NodeJS
If you don’t already have NodeJS installed, download it from here: https://nodejs.org/en/
Setting up
Let’s get started by cloning the Node.js barebones to the system.
1. Open up the terminal and execute the following command. This will clone the repo to your
machine. (Don’t have GIT installed?)
git clone https://github.com/heroku/node-js-sample.git
2. Great, let’s move into the directory you just cloned:
cd node-js-sample
3. Use node package manager to install all the packages you’ll need to have the server running
on the machine (If this does not work on your machine, then you don’t have Node.js and NPM
installed. If you haven’t done already, go to https://nodejs.org/en/download/ and download
Node.js for your system. Once it is installed, node and npm will be two recognizable commands
in the terminal.)
To install the packages, run the following command:
npm install
4. Next, start the server:
npm start
If you followed the steps correctly, it should have the server started on port 5000 on your
machine. To test, open up a browser and navigate to localhost:5000. Wait, what is localhost?
You should see something like this:
Let’s see what just happened. Open up the file called index.js. You should see:
app.get(‘/’, function(request, response) {
response.send(‘Hello World!’)
})
This is called a router – it looks for all the requests of the type: localhost:5000/*
and returns ‘Hello World!’, which your browser prints.
Let’s start by modifying this a bit. Change it to:
app.get(‘/:input’, function(request, response) {
response.send(request.params);
})
What we did here is we printed the “param” attribute Save your index.js and navigate to
localhost:5000/databasesarecool
Wait it does not work? Of course. You need to restart your server. Whenever you make a
change to index.js file, you need to shut down the server (Ctrl + C) and start it again.
Now navigate to the page. It should display the text you just typed in.
If you’ve never worked with Node.js before, check out this article on how callbacks work, since
they are the basis of everything in Node.
Now let’s try pulling and visualizing NYC Bike-share data, to see how to work with data that
exists in JSON format.
New York is obsessed with data. Citi Bike has a live feed of their bike stations and the number
of bikes across the city. Our goal is to be able to show that on our page.
Go to: https://feeds.citibikenyc.com/stations/stations.json
Here’s the format of the JSON:
{
“id”: 72,
”stationName”: ”W 52 St & 11 Ave”,
“availableDocks”: 25,
“totalDocks”: 39,
“latitude”: 40.76727216,
“longitude”: -73.99392888,
“statusValue”: “In Service”,
“statusKey”: 1,
“availableBikes”: 13,
“stAddress1”: “W 52 St & 11 Ave”,
“stAddress2”: “”,
“city”: “”,
“postalCode”: “”,
“location”: “”,
“altitude”: “”,
“testStation”: false,
“lastCommunicationTime”: “2018-02-09 04:57:02 PM”,
“landMark”: “”
}
We’re only interested in [‘stationName’, ‘availableDocks’, ‘totalDocks’, ‘availableBikes’,
statusValue, ‘lastCommunicationTime’]. Let’s make some changes so we can fetch the JSON,
and show it on the page.
In index.js, modify the router so that is looks like this:
app.get(‘/bikes’, function(request, response) {
response.sendFile(path.join(__dirname, ‘/’, ‘bikes.html’));
})
Add another router, which returns a JavaScript file when it is fetched: (Note that we need to do
this because all of the requests are going through Node.)
app.get(‘/script.js’, function(request, response) {
response.sendFile(path.join(__dirname, ‘/’, ‘script.js’));
})
Make a new file called bikes.html. It should look like this:
<body ng-app="NYCBikes"> <div ng-controller="bikeController"> <p ng-repeat="x in data">{{x}}
Note the ng-app, ng-controller and ng-repeat directives. Also note that we’re importing three
scripts here: AngularJS, JQuery and script.js, which will print the CitiBike JSON.
Finally, add a new file called script.js. It should contain the following code:
var app = angular.module('NYCBikes',[]);
app.controller('bikeController', function($scope, $http) {
var request =
$http.get("https://feeds.citibikenyc.com/stations/stations.json");
request.success(function(data) {
$scope.data = data.stationBeanList;
});
request.error(function(data){
console.log('err');
});
});
Note that it defines an app, NYCBikes and contains a controller called bikeController which
fetches the JSON and returns the content to the $scope variable.
Make sure you understand how the pieces combine to form the webapp.
The next step for you is to implement the following:
1. Convert the raw JSON to a table. It should have the following columns:
Station
Name
Available
Docks
Total Docks Available
Bikes
Last
Communica
tion
Status
Refer to: https://www.w3schools.com/angular/angular_tables.asp
2. Add a search box above this table to allow searching the Station by name. The records
should filter as soon as the text in the textbox changes.
Working with SQL and NodeJS
Now for the database. We’ll add a MySQL component to our webapp in order to query data from
a MySQL database and display it to the user. MySQL is very similar to OracleDB, and you
should start by viewing the tables in SQLplus as you did for HW1.
Here are the details to upload and set up the database on your own fling.seas account. You can
then query the database in the same way as you did for the database in HW1, and we will show
you how to query the database from Node.js.
Create a MySQL database on your SEAS account using the instructions at this link.
You will host the database on your own fling.seas.upenn.edu. Download FOF.sql from this link
and run FOF.sql while in terminal in your fling MySQL database. This will automatically create
the tables and import all the database’s data into your MySQL database.
These are the details of your database connection that you can refer to:
Host fling.seas.upenn.edu
User (your SEAS login)
Password (your password)
Database (the database name you chose)
Play around with the database a bit. Print out the rows of each table to understand the schema
and the data that you have. The table names are Person, Friendships, and Family. Again, you
can do this using the method from HW1.
Now we want be able to query the database from our Node.js application. First, we need to
install the MySQL package on our machines. Make sure you’re in your “node-js-sample”
directory.
Npm install mysql
Start reading through the documentation for this package in order to understand the syntax and
functions you have available.
Write the code necessary to establish a connection to the database. For reference:
https://www.npmjs.com/package/mysql#establishing-connections
Write a query to do the following. Again, the documentation describes how to write MySQL
queries in Node.js:
Write a query to output the number of friends that each person in the “person” table has.
The query should output the name of the person and the number of friends he/she has.
Test the results of your query by printing the datastructure in console. What does it look like? If
you’ve never done this before, print statements appear on the client side if you right click on
your webpage in your web browser and click “Inspect.” Let’s convert the results into JSON so
that we can display the results just as you did with the Bike-share data! Using the same process
as before, display the results of your query as a table. Do all of this in a new html file called
friendships.html and output the results there.
Now we will write queries that change dynamically based on user input. Create a text box using
HTML elements on your friendships.html page and create a button that initiates a query using
the contents of the text box as a variable. The query should do the following:
Write a query to output all details (login, name, role, sex, relationshipStatus, and
birthyear) of all the family members of input person. Your code should be able to handle
the case where the input person is not found in the “Family” table.
Make sure to test the results of both of your queries! You can connect to the database in the
same way that we did for HW1 in order to test the queries before you implement them in Node.js
For submission, we would like for you to submit your code pertaining to the two queries as well
as screenshots of the output when displayed on a webpage.