DSCI 559 Homework #3: ER Modeling 数据管理代写 Draw an ER diagram that captures the following structure. You can include some descriptive text to explain features of your model if you ...View details
DSCI 559 Lab #4: PostgreSQL
数据库课业代写 Description: For this lab, we will be setting up PostgreSQL and loading the DVD Rental database in your PostgreSQL. The DVD Rental Database holds
For this lab, we will be setting up PostgreSQL and loading the DVD Rental database in your PostgreSQL.
The DVD Rental Database holds information about a company that rents movie DVDs. The schema for the DVD Rental database has been attached:
There are 15 tables in the DVD Rental database along with E-R Diagram:
actor – stores actors data including first name and last name.
film – stores films data such as title, release year, length, rating, etc.
film_actor – stores the relationships between films and actors.
category – stores film’s categories data.
film_category– stores the relationships between films and categories.
store – contains the store data including manager staff and address.
inventory – stores inventory data.
rental – stores rental data.
payment – stores customer’s payments.
staff – stores staff data.
customer – stores customers data.
address – stores address data for staff and customers
city – stores the city names.
country – stores the country names.
Please refer to the attached installation notes for installation in macOS or windows.
Checking for Successful Postgres Installation:
1. Check that you have a Postgres SQL installed on your system. Just open SQL Shell (Search in Windows or Spotlight in Mac). Keep pressing enter untill you see ‘Password for user postgres:’
2. Enter your password to process into your database (use the password that you entered during installation steps).
3. Execute Query Select version(); to check your Postgres SQL is installed correctly.
Setting up DVD Rental Database:
1. In the SQL shell you opened previously execute command:
CREATE DATABASE dvdrental;
2. Click on the link below to get your DVD rental database zip:
3. The database file is in zip format ( dvdrental.zip) so you need to extract it to dvdrental.tar before loading the sample database into the PostgreSQL database server.
4. Open command prompt/terminal and Then, navigate the bin folder of the PostgreSQL installation folder: C:\>cd C:\Program Files\PostgreSQL\11\bin (for Windows)
5. Refer to the screenshot below after you cd into the PostgresSQL\11\bin folder to make sure you are on the correct path.
6. After that, use the pg_restore tool to load data into the dvdrental database (type in below command):
pg_restore -U postgres -d dvdrental C:\dvdrental\dvdrental.tar
7. Enter your password if prompted (the one you used while installation) to enter into your database.
8. Now search for pgAdmin in your Windows Search/Mac Spotlight and launch the program.
9.Next, right-click on the dvdrental database and choose Restore… menu item as shown in the following picture:
10.Navigate to the path were you extracted dvdrental.zip to get dvdrental.tar
11.Click on Restore. After that, wait for a few seconds to let the restoration process completes (Note: if you get any error/warning don’t worry keep going we will verify that your DB setup worked fine or not).
12.Finally, open the dvdrental database from object browser panel, you will see the tables in the public schema and other database objects as shown in the following picture:
13. Right-click the dvdrental database and select query tool (or open Tools -> Query Tool on dvdrental database).
Execute (F5 keyboard shortcut) SQL query to show title, release_year, rental_rate, rating of the top 5 movies with the highest rental_rate.
Screenshot the whole page including the SQL code and Data output for submission.
Submission on Blackboard: 数据库课业代写
A single word file FirstName_LastName_lab4.word containing the screenshot in Task 13.
Late submissions (up to 24 hours) will be penalized by 20%. No credit will be given after 24 hours of the submission deadline.