Task $2(2$ marks$)$

Discovering the redundancies in the relational tables

Analyze a collection of incorrectly designed relational schemas listed below. Please note,

that all relational schemas are in $1$ NF $.$ If you do not remember what does it mean please

refer to CSIT$115$ lecture slides.

ORDER$($customer#$,$ order#$,$ order$-$date, part#$,$ quantity$)$

A relational table ORDER contains information about the parts ordered by the customers

$($customer#$).$ An order is uniquely identified by an order number $($order#$).$ A part is

identified by a part number $($part#$).$ An order consists of one or more parts. An order is

submitted by a customer on a given day $($order$-$date$).$ A quantity of each part is

determined an attribute quantity.

STUDENT $($student$-$number,

total$-$credits$-$required$)$

A relational table STUDENT contains information about the students. A student is

described by a student number $($student$-$number$)$ and full name $($full$-$name$).$

Each students is enrolled in one degree $($attribute degree$).$ An attribute

total$-$credits$-$required contains information about the total number of credits

that must be collected by a student to get a degree.

CONNECTION$($origin$-$city,

address$-$of$-$bus$-$station$-$in$-$origin$-$city,

destination$-$city,

address$-$of$-$bus$-$station$-$in$-$destination$-$city,

ticket$-$price$)$

A relational table CONNECTION contains information about the bus connections

provided by a transportation company. A description of a bus connection from an origin

city $($origin$-$city$)$ to a destination city $($destination$-$city$)$ includes an

address of bus station at an origin city

$($address$-$of$-$bus$-$station$-$in$-$origin$-$city$)$ and an address of bus station at

a destination city $($address$-$of$-$bus$-$station$-$in$-$destination$-$city$)$ and

ticket price $($ticket$-$price$).$ Assume that each city has one bus station.

SUBMISSION$($subject$-$code, assignment#$,$ student#$,$ full$-$name,

submitted$-$file$)$

A relational table SUBMISSION contains information about the submissions of

assignments implemented by the students. A description of an assignment consists of a

subject code $($subject$-$code$),$ assignment number $($assignment#$)$ and a path to a

submitted file $($submitted$-$file$).$ The submitted files are stored in a different

location. Each submission consists of one file. A student is described by a student number

$($student#$)$ and full name $($full$-$name$).$

Use a method of row insertions explained in a presentation $01$ Database Design Quality to

find the redundancies in the relational tables. Insert into the relational tables with the

schemas $($headers$)$ listed above from $3$ to $5$ rows to demonstrate the redundancies.

Include into a file solution$2.$pdf the drawings of relational tables with redundancies

and briefly explain the reasons behind each redundancy. The scanned and neat

hand$-$drawings$/$hand$-$writings are acceptable.

Deliverables

A file solution$2.$pdf with the drawings of relational tables with redundancies and

the brief explanations of the reasons behind each redundancy.