University IT354: Database Design & Management Assignment Solution

This document presents a comprehensive solution to a database design and management assignment (IT354). The solution includes an Entity-Relationship (EER) diagram for a social welfare service, followed by the relational model representation. It details the attributes, data types, sizes, primary keys, and foreign keys for several relations, including member, rental_transaction, service_terminal, and parking_rack. The assignment addresses database normalization, identifying and decomposing un-normalized relations and functional dependencies. It also provides SQL queries for retrieving specific data from the database, such as selecting trainers based on fees and members based on duration. Finally, the assignment includes a bibliography of relevant sources on database systems and design.

Running Head: DATABASE DESIGN & MANAGEMENT
DATABASE DESIGN & MANAGEMENT (IT354)
TERM 3, 2017
ASSIGNMENT
[Name of the Student]
[Name of university]

1DATABASE DESIGN & MANAGEMENT
Table of Contents
QUESTION 1 2
QUESTION 2 3
QUESTION 3 4
QUESTION 4 6
QUESTION 5 7
Bibliography 8

Running Head: DATABASE DESIGN & MANAGEMENT
QUESTION 1
Figure 1: Showing EERD for Singapore’s Social Welfare Service
(Source: Created by author)

Running Head: DATABASE DESIGN & MANAGEMENT
QUESTION 2
Figure 2: Showing relations for EERD of Singapore’s Social Welfare Service

1DATABASE DESIGN & MANAGEMENT
(Source: Created by author)
QUESTION 3
Relation Attribute Data
Type Size Primary
Key
Foreign
Key
Referred
Relation
member memberID Varchar 20 Yes No Non
member name Varchar 50 No No Non
member email Varchar 50 No No Non
member phone_number number 10 No No Non
Member pin number 6 No No Non
Member state varchar 20 No No Non
Member country varchar 20 No No Non
Member Street varchar 20 No No Non
Member membership_status varchar 10 No No Non
Member member_fee number 10 No No Non
Relation Attribute Data
Type Size Primary
Key
Foreign
Key
Referred
Relation
rental_transactio
n trnsNum Varchar 20 Yes No Non
rental_transactio
n Bicycles_ID Varchar 20 No Yes Bicycles

2DATABASE DESIGN & MANAGEMENT
rental_transactio
n memberID Varchar 20 No Yes member
rental_transactio
n pick_up_time Datetime No No Non
rental_transactio
n Pick_location Varchar 200 No No Non
rental_transactio
n drop_off_time Datetime No No Non
rental_transactio
n drop_location Varchar 200 No No Non
rental_transactio
n total_rental_length Number 10 No No Non
Relation Attribute Data
Type
Siz
e
Primar
y Key
Foreig
n Key
Referre
d
Relation
service_termina
l
ID_Num Varcha
r 20 Yes No Non
service_termina
l
terminal_name
Varcha
r 50 No No Non
service_termina
l
street_address
Varcha
r 200 No No Non
service_termina
l
telephone_numbe
r number 10 No No Non
service_termina
l
storage_capacity
number 10 No No Non
Relation Attribute Data
Type
Siz
e
Primary
Key
Foreign
Key
Referred
Relation
parking_rack rack_numbe Varchar 20 Yes No Non

3DATABASE DESIGN & MANAGEMENT
r
parking_rack name Varchar 100 No No Non
parking_rack location Varchar 200 No No Non
parking_rack capacity Number 10 No No Non
QUESTION 4
A. the identified single un-normalized relation is personal-trainers.
B. Relation personal-trainers: id; members: members_ID.
C. identified functional dependency:
1. Members_ID -> first_name, last_name, date_of_birth, monthly_fee, address,
phone_number
2. Members_ID -> personalTrainerName, fees, duration.
D. Decomposed relation:
1. Member (Members_ID, first_name, last_name, date_of_birth, monthly_fee,
address, phone_number, trainer_ID, duration)
2. Trainer (trainer_ID ,personalTrainerName, fees)
QUESTION 5
Query 1: Select * from trainer where fees > 2000;
π trainer_ID,personalTrainerName,fees (σ fees >2000 (trainer))

4DATABASE DESIGN & MANAGEMENT
Query 2: select * from member where duration > 6;
π Members_ID, first_name, last_name, date_of_birth, monthly_fee, address,
phone_number, trainer_ID, duration (σ duration >6 (Member))

5DATABASE DESIGN & MANAGEMENT
Bibliography
Abadi, D., Boncz, P., Harizopoulos, S., Idreos, S., & Madden, S. (2013). The design
and implementation of modern column-oriented database
systems. Foundations and Trends® in Databases, 5(3), 197-280.
Bosc, P. (Ed.). (2013). Fuzziness in database management systems (Vol. 5). Physica.
Chen, Z., Cafarella, M., Chen, J., Prevo, D., & Zhuang, J. (2013). Senbazuru: a
prototype spreadsheet database management system. Proceedings of the
VLDB Endowment, 6(12), 1202-1205.
Chung, T. Y., Mashal, I., Alsaryrah, O., Hsu, T. H., Chang, C. H., & Kuo, W. H.
(2014, July). Design and implementation of light-weight smart home gateway
for Social Web of Things. In Ubiquitous and Future Networks (ICUFN), 2014
Sixth International Conf on (pp. 425-430). IEEE.
Corbett, J. C., Dean, J., Epstein, M., Fikes, A., Frost, C., Furman, J. J., ... & Hsieh, W.
(2013). Spanner: Google’s globally distributed database. ACM Transactions
on Computer Systems (TOCS), 31(3), 8.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, &
management. Cengage Learning.
DeBrabant, J., Pavlo, A., Tu, S., Stonebraker, M., & Zdonik, S. (2013). Anti-caching:
A new approach to database management system architecture. Proceedings of
the VLDB Endowment, 6(14), 1942-1953.
Elmasri, R., & Navathe, S. B. (2015). Fundamentals of database systems. Pearson.

6DATABASE DESIGN & MANAGEMENT
Jones, P., Binns, D., Chang, H. Y., Fraser, M., Li, W., McAnulla, C., ... & Pesseat, S.
(2014). InterProScan 5: genome-scale protein function
classification. Bioinformatics, 30(9), 1236-1240.
Moniruzzaman, A. B. M., & Hossain, S. A. (2013). Nosql database: New era of
databases for big data analytics-classification, characteristics and
comparison. arXiv preprint arXiv:1307.0191.
Paradies, M., Lehner, W., & Bornhövd, C. (2015, June). GRAPHITE: an extensible
graph traversal framework for relational database management systems.
In Proceedings of the 27th International Conference on Scientific and
Statistical Database Management (p. 29). ACM.
Stonebraker, M., Brown, P., Zhang, D., & Becla, J. (2013). SciDB: A database
management system for applications with complex analytics. Computing in
Science & Engineering, 15(3), 54-62.