Using ER syntax, draw a single integrated conceptual model for the mini$-$world described below.

A bike shop needs a database to manage their sales of parts and service. Here are some of the things

they need to track:

Customers are billed and pay in terms of an invoice, describing the parts $($or items$)$ sold and

work done for a particular transaction.

Each invoice is sold by a particular employee, the one who rang up the final transaction.

On a given invoice, customers can buy various items $($e$.$g$.$ parts, accessories$).$ Work performed

will also appear line items on the invoice.

Items sold have a description, a quantity, and a unit price. They also have an item

ID$/$SKU to tie it into an inventory system.

Work is performed by a particular employee, has a description, and a cost.

Tip: work can also be modeled with a quantity and unit price; an hourly billing rate for

general labor, and a quantity of $1$ for work with a standardized billing rate, such as $$25$

for a brake tune$-$up$.$

Work is also performed on a specific bicycle.

Customers can have more than one bicycle.

Invoices can be open, meaning they are still accumulating items, or closed, meaning they are

finished and ready to be paid.

Track basic information about customers $($name$,$ billing address, phone number, etc.$)$

Simplifying Assumptions

Each invoice is sold to a single customer.

Each bicycle is owned by a single customer.

We do not need to model an inventory or payroll system. Describe a set of relations that will store the data from your ER model in part $(1).$

For each relation, give:

Its name

The entity or relationship type it stores

Its attributes, along with their approximate data type $($number$,$ string, date$)$

Its primary key

Its foreign key$($s$),$ and the relations that they reference.

$+5$ Bonus points if you include SQL table schema definitions. For each of your relations in part $(2),$ give $2-5$ example rows.

All data must be referentially valid $($that is$,$ a foreign key value in one table must refer to a valid

primary key value in the table that it references$).$ Utilize the generalization construct of the enhanced entity$-$relationship model, where appropriate, to

demonstrate understanding of the concept. For each case, specify the attributes of the entities

involved and indicate the type of generalization. Address any instances of overlapping if they arise.

$4\mathrm{.}1(20$ points$)$ An international airline offers flights, each of which has a number that identifies the

flight $($for example Paris$-$Boston$),$ a date $(25$ March $2019),$ a departure time $(8$:$00$am$)$ and an arrival

time $(12$:$00$pm$),$ a departure airport and an arrival airport. There are national and international

flights. The international flights can have one or more stopovers.

$4\mathrm{.}2(10$ points$)$ Describe a set of relations that will store the data from your ER model from Exercise

$4\mathrm{.}1.$

Use always Chen's notation