Database Systems ER model, Mapping E/R to Relational Schema

Database Systems ER model, Mapping E/R to Relational Schema
Database systems General remark: to draw E/R models on your computer, you can use yEd 1. If you decide to do so, you can use the provided template ER -master.graphml . Of course, you can use any other tool of your choice, or even draw by hand. Exercise 1 (ER model – Cruises ) In the following example ship cruises should be modeled. Please use the Chen notation . Pay atten – tion to the following facts for the model and be sure to map all these points (including the keys): a) A cruise has a start and end date and is carried out by a ship. This has a name and a maximum capacity, and can of course be used for several cruises. b) Each ship has a certain number of cabins with a certain size, a description and a unique number for each ship (for example, there may be a cabin numbered 123 on several ships). c) Each cabin is assigned to a category that determines the type of cabin. Here is the name and the price / day recorded, as well as whether the cabin category has a balcony and / or a Jacuzzi. d) Name and address are recorded for passengers. Passengers book the n (possibly several) cab – ins for certain cruises. The system should record when a passenger has made the booking. e) Each cruise offers any number of activities (such as excursions or sports activities) that pas – sengers can book in addition. For each activity the start / end date as well as a title and the price are saved. 1http://www.yworks.com/products/yed longitude social# name name latitude type n n n knows n n n n near to seen isa agent# n date distance time description Agent Person Location Exercise 2 (Mapping E/R to Relational Schema ) Consider the given E/R diagram about observations of an intelligence agency. In the model loca – tions, perso ns and agents are stored. Furthermore, it is stored who knows each other and observed meetings of two persons. Map the E/R diagram to a relational schema and create a primary and foreign key table as shown in the lecture. 2