本次代写是一个数据库查询相关的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.
3) Translate each query below into SQL: [8 marks]
(viii)Find all employees who earn more than the average salary of all employees of their company.
(ix) Find the company with the most employees.
(x) Find the company with the smallest payroll.
(xi) Find those companies which pay more, on average, than the average salary at NYZ Bank Corporation.
程序代写代做C/C++/JAVA/安卓/PYTHON/留学生/PHP/APP开发/MATLAB
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: itcsdx@outlook.com 微信:itcsdx
如果您使用手机请先保存二维码，微信识别。如果用电脑，直接掏出手机果断扫描。
