数据库代写 | Comp2411 Database Systems

本次代写是关于数据库查询相关的assignment

Question A. (Totally 30 marks)

1) A database is about to be constructed in order to keep the records of all ferry companies, boats, and trips (short/medium/long distance) operated in a water region. Each company has a number of boats. Each boat has a unique plate-ID number, model, and other information (like number of seats and so on). For each trip, it is required for insurance purpose to keep track of the ferry company and actual boat involved in each trip, and the departure and arrival ports, etc. Design an ER schema diagram for this application, stating any assumptions you’d like to make.[10 marks]

2) For your ER diagram given above, convert it into a relational schema using the mapping guidelines discussed in the lectures. For each relation (table) obtained, specify the name and its attributes, as well as its primary key.[5 marks]

3) When designing an ER diagram for a particular enterprise/application, you can have several alternatives from which to choose. The figures below shows 3 alternative database designs for a university academic registrar (AR) office which maintains data about each class, including the instructor, the enrollment, and the time and place of the class meeting. For each student-class pair, a grade is recorded. Please compare and contrast the designs in terms of the merits of each, and shortcomings if any. (Hint: you may consider from the perspectives of possible queries to be received and processed, linking up with the real-life application scenarios.)[15 marks]

Question B. (Totally 30 marks)

A relational database consisting of four relations (tables) is created below for a banking enterprise:

CREATE TABLE LIVES (Person-name CHAR(20) NOT NULL,

Street CHAR(20),

City CHAT(20),

PRIMARY KEY (Person-name));

CREATE TABLE LOCATED-IN (Company-name CHAR(20) NOT NULL,

City CHAT(20),

PRIMARY KEY (Company-name));

CREATE TABLE WORKS (Person-name CHAR(20) NOT NULL,

Company-name CHAR(20) NOT NULL,

Salary NUMBER(8,2),

PRIMARY KEY (Person-name, Company-name),

FOREIGN KEY Person-name REFERENCES LIVES,

FOREIGN KEY Company-name REFERENCES LOCATED-IN);

CREATE TABLE MANAGES (Person-name CHAR(20) NOT NULL,

Manager-name CHAR(20) NOT NULL,

Duration Integer,

PRIMARY KEY (Person-name, Company-name),

FOREIGN KEY Person-name REFERENCES LIVES,

FOREIGN KEY Manager-name REFERENCES LIVES(Person-name));

1) Translate each SQL query below into: [18 marks]

• the relational algebra, and

• plain English (i.e., natural language) statement

(i) SELECT Person-name FROM WORKS WHERE Company-name = “NYZ Bank Corporation”;

(ii) SELECT r.Person-name, r.City

FROM LIVES r, WORKS w

WHERE w.Company-name = “NYZ Bank Corporation AND w.Person-name = r.Person-name;

(iii) SELECT * FROM LIVES

WHERE Person-name IN (SELECT Person-name

FROM WORKS

WHERE Company-name = “NYZ Bank Corporation”

(iv) SELECT r.Person-name

FROM LIVES r, WORKS w, LOCATED-IN i

WHERE r.Person-name = w.Person-name AND r.City = i.City

AND w.Company-name = i.Company-name;

(v) SELECT r.Person-name

FROM LIVES r1, LIVES r2, MANAGES m

WHERE r1.Person-name = m.Person-name AND m.Manager-name = r2.Person-name

AND r1.Street = r2.Street AND r1.City = r2.City;

2) Translate each query below into the relational algebra: [4 marks]

(vi) Find all employees and their cities for those who do not work for NYZ Bank Corporation (assuming that people may work for zero or several companies).

(vii) Find all employees who earn more than every employee of Hang Seng Bank.


程序代写代做C/C++/JAVA/安卓/PYTHON/留学生/PHP/APP开发/MATLAB


本网站支持淘宝 支付宝 微信支付  paypal等等交易。如果不放心可以用淘宝交易!

E-mail: itcsdx@outlook.com  微信:itcsdx


如果您使用手机请先保存二维码,微信识别。如果用电脑,直接掏出手机果断扫描。

blank

发表评论