Sheet 01 Homework Solution PDF

Preview

Summary

Exercise Sheet 01 - Homework Solution for Week 1...

Description

Exercise Session for Introduction into Computer Science (for non Informatics studies, TUM BWL) (IN8005), WS 2021/2022

Sheet 1 Homework are to be worked out independently. Submission is done via moodle. https://www.moodle.tum.de/course/view.php?id=69095 Deadline for submission is 24:00 o clock on 07.11.2021. If you have any questions regarding your solution after comparison with the sample solutions, you may post them in a comprehensive way in the moodle forum. Each assignment is marked to give an estimation of its difficulty and/or necessary effort. – * Simple and little effort, – ** Difficult or at least requires some work, – *** Very Difficult or extensive.

Databases Overview Entity-Relationship-Model: Conceptual modeling of databases.

1

ER-Models Overview Entities Relations – binary – ternary – n-ary – recursive – roles Attributes Keys Functionalities

Tutorial 1.1: Functionalities — Human resource management ** For human resource management it is necessary to keep track of fundamental employee data. The following incomplete ER model is given.

The following basic conditions apply: An employee can have a maximum of 2 superiors. 2

Each employee must occupy at least one position. There can be temporarily vacant positions. There may be cost centers temporarily without assigned positions. Positions cannot belong to more than one cost center. Enter functionalities!

Solution

Tutorial 1.2: Modeling — Employee office scheduling * Model an allocation schedule for office rooms. Employees (employee number, name, ...) are sitting in rooms (characterized by a room number, number of places, ...) for a period of time in a certain place. Do not forget the functionalities!

3

Solution

Tutorial 1.3: Functionalities — MVG *** Given the following incomplete ER diagram of a one year planning for the Munich transport group.

Enter useful functionalities for the relations! Use the following assumptions: Each driver drives a maximum of one vehicle per day (Each line operates every day of the year. Vehicles can be out of service for a complete year. Drivers can be invalid for a complete year. There are 60 lines in total) 4

Solution We simply go through all possible functionalities and check, if it makes sense. Driver × Line × V ehicle → Date not possible, since the same driver can ride the same vehicle on the same line several days a year. Line × V ehicle × Date → Driver not possible, since the same line can be operated with the same vehicle and on the same day by different drivers. V ehicle × Date × Driver → Line not possible, since the same driver can drive on the same day and with the same vehicle on several lines. Date × Driver × Line → V ehicle possible, since the same driver can drive on the same day independently of the line with only one vehicle. Therefore, the following functionalities are inserted:

Homework 1.1: Functionalities — Flight Booking ** Add reasonable functionalities to the following ERM.

5

Aircraft

ID

type

operated with

ID

leave time

ID from Pilot

flown by

IATA CODE Airport

Flight

city

to

arrival time

has

booking code

Ticket

6

booked by

Customer

ID

Solution

Aircraft type

ID

1

operated with

ID

leave time

ID N N Pilot

1

flown by

N

from

1

IATA CODE Airport

Flight N

to

city

1

1

arrival time

has

N N booking code

Ticket

7

booked by

1 Customer

ID

Homework 1.2: Modeling — Dating App * Model a database for a dating app where a user scrolls through others’ profiles and can send them a “like”. Each user has to insert a unique nickname, their age, and their gender. The system keeps track of the “likes”. Provide an ER-Model and do not forget to add an ID for each entity and functionalities for each relation. Hint: specify who is sends the “like” and who receives it.

Solution

Homework 1.3: Modeling — Grocery Delivery ** Model a database for a grocery delivery app. The users of the app can place orders for some products and get them delivered at a desired address. For each ordered product, the user specifies the quantity. The system also allows the users to save a list of favorite products. For each product, the system also records how much of it is still in stock. Each order is delivered by a specific rider at a certain time during the same day. Provide an ER-Model including all the mentioned information. Do not forget to add an ID for each entity and functionalities for each relation!

8

Solution

9...