BS DEGREE IN INFORMATICS ENGINEERING Academic year: 2018/2019 – 2nd year, 2nd term
Subject: File Structures and Databases
First Assignment: Relational Design and Implementation
Problem Description
The company VinylCorp is dedicated to the cataloging and sale of musical works recorded on vinyl. They already have a storage system oriented to the acquisition of information, but when it comes to retrieve information it is not efficient nor convenient. Thus, they want a good design avoiding redundancies and inconsistencies, saving space and showing acceptable efficiency.
In this system, there is a storage of musical themes and songs (hereinafter, themes), including the theme’s title, location on the disc (side and track), length, author of the theme, vinyl identifier (ISVN, standing for international standard vinyl number), title of the album that includes the theme, artist who interprets it (can be a group or a soloist), the artist’s nationality and language, members of the group and their roles (if it is a group, up to five members; if it is a soloist, just one member), vinyl format (long play or single), rotation speed, hole size, record company that publishes it, telephone of this publisher, album’s launching date, quantity of copies of the album made for its launching, and copies made in total. In addition, it also registers who is the manager of the artist (soloist or group) at the time of editing that album (name, surnames, telephone), the recording studio (its name, address, and the name and surname of the technician responsible for the studies). Finally, it also has some information regarding both the cover (graphic company, address, photographer, draftsman, layout artist) and the back cover (with the same data elements).
The amount of copies that are made of the album is only recorded in case it is a long play (it is not observed for singles). Instead, the singles record other information: what position they have reached in the charts (peak) and how many weeks they stayed in that peak position. It should be noted that the singles are always associated with an album, and contain at least one theme of it (which, moreover, is often the title of the single).
Groups are not always static: eventually, one of the members can decide to leave the group, to start his journey alone or to retire permanently. When this happens, the dropper’s place (and role) in the group is usually occupied by another musician. But it could also occur that the group goes on with one member less. Reversely, when any artist finds his/her soloist career disappointing, he/she could be joining a group (either a new one or an already existent one). In case the artist is a dropper, he/she could even be rejoining his/her old teammates.
Everything related to these issues (we have talked so far) was in the old DB gathered into a single non-normalized table, which name is vinyl. Art should never be censored, but this company eventually gives the cold shoulder to a swellhead artist, by removing him/her from the base. In such case, all the related soloist works will also be removed, the songs he/she wrote will be banned (and removed from everywhere), and his/her membership to groups will also be forgotten. There cannot be a vinyl’s side without tracks so, if that happens, the whole album/single is removed.
BS DEGREE IN INFORMATICS ENGINEERING Academic year: 2018/2019 – 2nd year, 2nd term
Subject: File Structures and Databases
First Assignment: Relational Design and Implementation
On the other hand, the themes are played on the radio, and they want to exploit this type of information. They have another table (labelled as hits) containing a sample of this, showing the following attributes: theme’s interpreter, title, date and time of emission, name of the radio station, address, URL of its website, email, and phone. However, the information comes from different sources, and consequently has different semantics: in some records, the first data element is the ISVN of the album (instead of the interpreter). These records (radio stations emitting music) shouldn’t be removed, so when any theme/song is banned (removed from the base) we will be modifying the record: instead the reference of the theme, we will be storing the well-known eternal hit ‘Moments’ by the famous group ‘Golondrinajes’.
Finally, they have another table (purchases) in which they have been recording data about clients (name, surname, ID, date of birth, telephone, email, postal address) and their purchases of albums (album title, artist, date of purchase, and delivery date). A client can perform several orders in as many days (only one per day). Any order can include several references, and can be updated: you can add more references during the day (in another day, the newly added references will belong to another order); and you can also remove some of them, as far as they have not been delivered (once delivered, you can’t cancel a reference from the order). If the whole order is cancelled, all its references will (but this is only possible if none of them is delivered).
Supporting Materials
Apart from laboratory and tutoring sessions, students will have the following:
• Documents:
o Problem description
o Slides
o Template for assignment report • Sw resources:
o User credentials for connecting DBMS Oracle
o Clients for interacting with Oracle (installed in computer rooms and
through aula virtual).
o Access to the old tables (with all data uploaded)
o Scripts for creating and populating old tables at another server, just in
case you can’t work with the University’s server (you’ll be able of creating the old DB at home).
BS DEGREE IN INFORMATICS ENGINEERING Academic year: 2018/2019 – 2nd year, 2nd term
Subject: File Structures and Databases
First Assignment: Relational Design and Implementation
1st Assignment – TO DO
Do a Relational Design meeting the requirements (as much as possible). Represent it by means of a relational graph (use notation provided in class), and implement it in SQL (on DBMS Oracle®).
Do a massive data upload (import) from the old DB to the newly created one. Notice there could appear errors during this process, due to the lack of semantic coverage in the former DB. You have to detect, analyze and describe them in the report. If possible, propose and implement some solution.
Document all your work (in a Labwork Report; you can use the template provided, just to ensure you’re not skipping some explanations).
Submit through Aula Global a compressed .zip file containing two scripts (creation.sql, upload.sql) and the report, preferabily in portable document format (report_1.pdf).
BS DEGREE IN INFORMATICS ENGINEERING Academic year: 2018/2019 – 2nd year, 2nd term
Subject: File Structures and Databases
First Assignment: Relational Design and Implementation
2nd Assignment – TO DO
Solve all the following queries in Relational Algebra, and implement them in SQL: a) …
Propose views completing the external design of the DB (enumerate and describe them). Implement at least two views of the following:
a) …
Incorporate semantics through triggers. In the following table, you can found four
categories, and several proposed triggers for each. You have to design (describe in natural language all parameters and the working of) at least one trigger of each category (including those proposed by the students). Finally, implement one of the already designed triggers (A, B or C).
Cat
Disparador
Descripción
A)
…
Check
B)
…
Check
C)
…
Check
D)
(proposed by students): …
.
Document all your work in a Labwork Report (you can follow the template). You have to gather a) design issues; b) implementation; and c) validation (every element has to be properly tested).
BS DEGREE IN INFORMATICS ENGINEERING Academic year: 2018/2019 – 2nd year, 2nd term
Subject: File Structures and Databases
First Assignment: Relational Design and Implementation
Optional Assignment – TO DO
Do non-compulsory parts of 2nd assignment (views and triggers).
Attain semantic completeness (incorporate any excluded semantics) through the design
and implementation of views and triggers.
Completeness is not required for submitting this part (not even for getting the full score).
Any contribution will be assessed, taking into account difficulty, quality and achievement.
BS DEGREE IN INFORMATICS ENGINEERING Academic year: 2018/2019 – 2nd year, 2nd term
Subject: File Structures and Databases
First Assignment: Relational Design and Implementation
3rd Assignment – TO DO
Implement a standard workload based in the queries and views of the second assignment on the complete database, to which actualization operations (insert/delete/update) will be added. The weekly workload is:
Note: remember to re-stablish initial state after testing, by deleting the new records.
Measure the workload performance (in both time consumption and number of accesses) through several repetitions. Analyze the running plan for each operation, extracting the different processes they involve. Classify and arrange all processes on each physical structure, stating frequency and cost approximation.
On that study, develop proposal leading to decrease the amount of accesses to secondary storage
Describe your complete physical design, stating which are the base structures involved, as well as the auxiliary structures used to improve performance.
Implement the former Physical Design; then, measure again the performance of the workload.
Compare performances (original physical design vs. new improved one), and draw the proper conclusions. Optionally, you can refine your design and re-implement it for taking again performance measurements, comparing them again with the previously taken.
Document all the process, paying special attention to the physical designs, performance measurements, execution plans, improvement proposals, and results comparisons.
Materials provided
Labwork sessions slides.
Templates (on Word .docx) for writing the assignments reports.
SQL Scripts (Oracle sintax): original database (1st assignment); new database and dataset
load (2nd assignment); workload and statistics related procedures (3rd assignment).
Oracle DBMS 11g account (one per labwork team).
o INSERT: …
o UPDATE: …
o QUERIES: five queries (1 to 5) in the second assignment