Scenario to ERD Rubel Sheikh

Scenario to ERD Rubel Sheikh

Exercise 1 UPS prides itself on having up-to-date information on the processing and current location of each shipped item. To do this, UPS relies on a company-wide information system. Shipped items are the heart of the UPS product tracking information system. Shipped items can be characterized by item number (unique), weight, dimensions, insurance amount, destination, and final delivery date. Shipped items are received into the UPS system at a single retail center. Retail centers are characterized by their type, uniqueID, and address. Shipped items make their way to their destination via one or more standard UPS transportation events (i.e., flights, truck deliveries). These transportation events are characterized by a unique scheduleNumber, a type (e.g, flight, truck), and a deliveryRoute. Please create an Entity Relationship diagram that captures this information about the UPS system. Be certain to indicate identifiers and cardinality constraints.

Exercise 1 UPS prides itself on having up-to-date information on the processing and current location of each shipped item. To do this, UPS relies on a companywide information system. Shipped items are the heart of the UPS product tracking information system. Shipped items can be characterized by item number (unique), weight, dimensions, insurance amount, destination, and final delivery date. Shipped items are received into the UPS system at a single retail center. Retail centers are characterized by their type, uniqueID, and address. Shipped items make their way to their destination via one or more standard UPS transportation events (i.e., flights, truck deliveries). These transportation events are characterized by a unique scheduleNumber, a type (e.g, flight, truck), and a deliveryRoute.

Exercise 1

Exercise 1

Exercise 2 A university registrars office maintains data about the following entities: courses, including number, title, credits, syllabus, and prerequisites; course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; students, including student-id, name, and program; instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Construct an E-R diagram for the registrars office. Document all assumptions that you make about the mapping constraints.

Entity and Attributes

Exercise 2

Exercise 2 ERD

Exercise 3 In our database, we have students. They have a name, a registration number, and a course of study. The university offers lectures. Each lecture may be part of some course of study in a certain semester. Lectures may have other lectures as prerequisites. They have a title, provide a specific number of credits and have an unique ID Each year, some of pool of all lectures are offered by a professor at a certain day at a fixed time in a specific room. Students may register for that lecture. Professors have a name and are member of a specific department.

procedure: Step 1: Find the basic entities Student Lectures Professor

Step 2: Find attributes for each entity – In our database, we have students. They have a name, a registration number and a course of study. – The university offers lectures. Each lecture may be part of some course of study in a certain semester. Lectures may have other lectures as prerequisites. They have a title, provide a specific number of credits and have unique ID – Professors have a name and are member of a specific department.

This model is really crappy! “Course of study” does not seem to be an attribute . Used by student and lecture. Even worse, lecture refers to a course of study in a specific curriculum semester. ---Use additional entity type with relationships! “Prerequisite lecture” also is not a good attribute . Prerequisite lectures are also lectures. ---Use a relationship instead! “Professor” does not have key attributes

Each year, some lectures of the pool of all lectures are offered by a professor at a certain day at a fixed time in a specific room. Students may attend that lecture.

After adding cardinality

Exercise 4 The scenario is that you are organizing an inter-university gliding competition, and you have decided to design a database to keep track of the administration of the competition. A number of universities have each entered a team in the competition (known as a gliding Task Week), and one of the things you need to keep track of is whether or not they have paid the entry fee. Each university team consists of a variable number of people who will take part in the competition; everybody who competes must be a member of one of the teams. The pilots will have different levels of experience. Some will be pre-solo, which means they can only fly as second pilot (known as crew capacity “P2”) in a two-seater glider. They can still compete for their team in this capacity, as long as there is an instructor flying with them as pilot-in-charge (crew capacity “P1”). Pilots who are of cross-country standard can fly as P2 just like pre-solo pilots, but may also fly on their own (flying “solo”) in any kind of aircraft. A pilot flying solo is always P1. Pilots who are instructors can fly solo in single- or two-seater gliders, or as P1 in a two-seater with a less experienced pilot. If two instructors are flying together they will simply decide between them who is P1 and who is P2. There are a number of different types of glider involved in the competition. Some are twoseaters, such as K7, K13, K21 and DG505. The rest are single-seaters; their types include K8, Pirat, DG300, Discus and LS4. There may be more than one glider of a particular type, but every glider can be distinguished by its callsign — a short string which is used to identify it in radio communications. Typical callsigns include “MF”, “P19”, “FNS” and “CPG”.

The competition is organised around tasks, which are routes that each competing glider must attempt to fly around. On each competition day a task is set for the pilots to fly in their gliders. The task is defined by choosing a set of turning points taken from a list available from the BGA (British Gliding Association). There are almost a thousand such turning points defined for the UK, and each has a unique trigraph or three-letter acronym to identify it. For example, “STI” is for Stirling and “LOM” is the Lake of Menteith. The competition is to be held at Portmoak Airfield (about 30 miles north of Edinburgh), which is “POR”. The task-setter will decide on a suitable task for each competition day, which will involve trying to glide from the starting point at the airfield around one, two or more turning points. For example, a set like “POR, STI, MVN” would define a triangle of just over 100km, with the corners at Portmoak, Stirling and Methven (which is near Perth). For the purposes of this example we will assume that competitors are allowed to fly around the turning points in any order they choose. As well as specifying the trigraph, the BGA list of turning points gives the latitude and longitude of each turning point, so their positions can be precisely identified on a map. Sometimes competitors can gain an unfair advantage by starting off much higher than other gliders, or by happening to pick a better time of day. The task-setter can therefore attach conditions to each task, specifying the maximum starting height allowed and the earliest time at which a glider can start.