数据库代写|Assignment 2: Databases

这是一篇英国的PostgreSQL设计实现数据库代写

 

1 Goals of the assignment

This assignment has been designed to assess your SQL skills on all different stages requested by the module syllabus; 1) database creation and implementation, 2) database population and 3) database querying. Hence, in this assignment, using SQL commands in postgreSQL you will:

  1. Create a database and connect to the database
  2. Implement the given design of the database
  3. Populate the database with the data facilitated in the assignment
  4. Perform the requested queries on the database.

To achieve these goals, we have organized the assignment in the form of one exercise per goal. Each exercise is further described below.

2 Preparing your submission

In order to solve the exercises described below, you will have to write several SQL commands. Write all these commands in a single text file with extension .sql. The file should be named:

<XXX>_Assignment2.sql

where <XXX> is the prefix of your student institutional email account at the University of Birmingham.

Submit a single .sql text file with all your commands for all exercises in order. This file will be executed in a 64-bit Debian container with PostgreSQL v13.4 using the \i command. For all exercises,assume a user account ’fsad’ with password ‘fsad2022’ permission to create databases already exists.This user also has permissions to read local files which will be important for Exercise 3 (see Sect. 4.3).Your code will be executed and evaluated using this common user account which means that those are the only permissions that “you” will have.

✑ Please note that whether the script run in your machine or not, is irrelevant. The script ought to run in the evaluator’s machine which is why we are giving you the exact configuration where you will be tested.

2.1 Style

In the .sql solutions file, clearly separate each exercise with comments e.g.:

1 /* *********************************************************

2 * Exercise 2. Implement the design of the Smoked Trout database

3 ************************************************************ */

Use meaningful names. Use adequate capitalization (of reserved SQL keywords, names in camel case, etc), spacing and comments to make your code readable. Not every attribute has to be NOT NULL. Attributes of type text or varchar do not have to be unnecessarily long. For instance:

Bad style

1 create table x ( theattribute int PRIMARY key , z varchar (255) NOT NULL );

2 Select z from x ;

Good style

1 CREATE TABLE meaningfulName (

2 objectID serial ,

3 objectName varchar (30) ,

4 PRIMARY KEY ( object ID ));

5

6 — Retrieve the names of the meaningfulName entities

7 SELECT objectName

8 FROM meaningfulName ;

3 Assessment

The assignment is made of 4 exercises weighted according to Table 1.

3 Assessment

The assignment is made of 4 exercises weighted according to Table 1.

Exercise

Weight [%]

Exer. 1 – Create database and connect 10%

Exer. 2 – Implement database 25%

Exer. 3 – Populate the database 25%

Exer. 4 – Query the database 40%

Table 1: Rubric

✑ Beware of cascading errors! In SQL, if one command fails, an error is raised, but the execution of the file is not stop. The failed command can have unexpected consequences on the outcome of subsequent commands. For instance, if the command to populate a table fails, and the table is left empty or with less records than it should, subsequent queries to that table will operate on incorrect information, and hence may produce wrong results.

4 The Scientific Monitoring Key for Taxed Trading Routes (Smoked Trout)

Emperor Knowledgeable VII of the Scientia galaxy is commisionning a new database to control and tax the trade routes on his vast empire to your company SmartStudent Ltd. The database ought to be able to fulfill the following requirements:

  • Trade routes are composed by a sequential list of ports of call and are identified by a monitoring key which is unique. They are operated by a interstellar shipping company and assigned a number of space ships to cover it, i.e. fleet size. Taxes on each route are charged at 12% over revenues of the last fiscal exercise, so each route has also to store the last exercise revenue in Experiments, the currency of the Scientifc empire.
  • Ports of call are space stations located at a certain longitude and latitude on a certain planet where products are bought and sold.
  • Planets are located in a star system and have a certain population. The most common way to refer to a planet is by its name, but beware! Different planets may have the same name (usually on different star systems but that is not a hard rule).
  • Any planet can have any number of space stations, but they all have at least one.
  • Planets are at a certain average distance with other planets measured in parsecs. One parsec is equal to about 31 trillion kilometers, which is longer than a light year!. The trade route length is just the sum of the planetary hops among the ports of call in the route visited in due visit order.Within planets distances among stations are considered negligible, that is, all stations in a given planet are a distance 0 parsecs from each other.
  • There are two types of products; raw materials and manufactured goods. All products have names, an origin (i.e. planet), occupy a certain volume per ton and have a certain value per ton.Raw materials are stored in a certain state of matter (gas, liquid, solid or plasma), can be either fundamental or composite, and have an associated extraction date. Manufactured goods in turn are made of a list of other products (whether raw materials or manufactured goods) and have a manufacturing date in the Universal Calendar, which oddly very much resembles the Western calendar on Earth.
  • Products are extracted or produced in batches originating at some planet. There can be repeated batches of the same product, and different batches of the same product may come from different origins. These batches are traded at the space stations. Not all batches may have yet been traded,but every sell is accompanied by a buy. Sells occur at a station of the batch origin i.e. in the same planet that produced it. Buys must occur at a station different of the selling station (whether in the same planet or other).Your database architect comes up with the conceptual design in Figure 1. You might need to make adaptations to the conceptual design in order to implement an adequate physical design.Table 2 is a list of the star systems ruled by the Scientific Empire, the inhabited planets and the space stations. In the assignment page, further find the data files with information about the traded products, space stations locations, trading routes, the batches, and the trading operations.