YodaBay Database Design, Implementation and SQL Queries Assignment

This assignment focuses on designing and implementing a database for YodaBay, an online sales platform, using Oracle SQL Developer. The task involves constructing a database, ensuring tables are in 3rd normal form, assigning primary and foreign keys, and resolving specializations. The solution includes a detailed description of the database design process, the use of top-down and bottom-up approaches, and the creation of an EERD diagram. The report presents table descriptions using the DESC command and lists primary and foreign key constraints. The assignment also addresses specific business questions using SQL queries, grants access to the database for a specified user, and implements both horizontal and vertical partitioning. Furthermore, it explores functional dependencies within the data and derives a key for the relation, providing a comprehensive database design solution.

Overview
Following the collapse of his airline company last year due to ongoing design faults with their
database, Jedi Warrior Yoda has decided to open an online sales platform called YodaBay. As chief
executive, Yoda, requires a database be constructed for this company. Unfortunately, Yoda knows
little about the database other than that the data recorded is typical for an online sales platform like for
example, eBay. Yoda instructs you ‘truth in the data itself may be found, believe in the functional
dependencies’.
If you are unsure of relationships between the data, the data itself is representative of the whole
spectrum.
Given the two data files and the data therein design and construct a database (including tables,
structures and data). You may use a top down or bottom up approach as you see fit; most likely you
will mix them. Specifically you must:
1. You must use oracle with sql-developer to construct the database,
2. The final tables must be in 3rd normal form,
3. Assign primary and foreign keys, and
4. Resolve all specialisations in a sensible manner.
Once you have constructed this database complete the questions below.
Problem 1:
Describe the process used to design your database. Note that there are many approaches that are
equally valid as long as they work; what I seek is for you to argue the case that the approach you
used was the appropriate one. Specifically, specify why you constructed the tables in the order you
constructed them. Explain why you used a top down or bottom up approach or a mix of the two. What
elements of the top down/bottom up approach did you use and why? If you used an initial EERD
mention this or for example if you created a new table because an existing table was not in a
particular normal form mention this.
Draw the EERD for your solution. In particular show the specialisations, entities, linking entities,
multiplicities and primary and foreign keys. To aid in your assignment report use template_EERD.ppt
as a template (it can be found in the assignment folder) to draw your EERD.
2

Problem 2:
Describe the tables you have constructed using the DESC Table command in SQL.
List the primary and foreign key constraints for your constructed tables using the following sql code
which will present them in a readable manner:
SELECT a.owner, a.table_name, constraint_type, a.constraint_name,
column_name, search_condition, r_constraint_name
FROM user_constraints A, user_cons_columns B
WHERE A.constraint_name = B.constraint_name
AND A.table_name IN ( <list your tables> )
ORDER BY A.table_name, constraint_type;
the output of these commands should be appended to your report.
Problem 3:
Yoda wishes to understand various aspects of his company and has four questions. These depend on
your data and the questions may be found in the file QXX.txt where XX is the last two digits of your i-
number. State the questions and their answers in your report.
Problem 4:
Yoda wishes to have an expert examine the database. Grant access to user dfay4 to the tables in
your database.
Problem 5:
Yoda wishes to distribute the database. You are required to do the following:
1. Identify one table that is a good candidate for horizontal partitioning. Partition this table, give
the SQL code for this partition. Explain why you chose to partition this table.
2. Identify a good candidate for a vertical partition. Implement this partition, give the SQL code
for this partition. Explain why you chose this partition.
Problem 6:
Consider the following attributes, which for clarity have been mapped to letters as:
basket_id A
basket_date B
name C
address D
item_id E
seller_id F
warranty G
price H
seller_country I
3

rating J
country K
country_id L
There is a functional dependency between the country and country_id, (if I know country I know the
country_id and also if know the country_id I know the country). I.e.
K --> L
L --> K
What other functional dependencies exist amongst these attributes (if you are unsure test your
hypotheses using your data). List them (your answer should use letters to denote the attributes and so
should look like: K --> L)
For the relation R(A,B,C,D,E,F,G,H,I,J,K,L), (i.e. the attributes above) and using the functional
dependencies you found above find, using the key determination algorithm, those attributes that are:
1. Definitely part of the key for R,
2. Definitely not part of the key for R, and
3. Maybe in the key for R.
Finally derive a key for R.
Following the collapse of his airline company last year due to ongoing design faults with their
database, Jedi Warrior Yoda has decided to open an online sales platform called YodaBay. As chief
executive, Yoda, requires a database be constructed for this company. Unfortunately, Yoda knows
little about the database other than that the data recorded is typical for an online sales platform like
for example, eBay. Yoda instructs you ‘truth in the data itself may be found, believe in the functional
dependencies’.
If you are unsure of relationships between the data, the data itself is representative of the whole
spectrum.