Database Design / Data Modelling 2002ICT/7003ICT

THE ASSIGNMENT IS IN PDF SO THE DIAGRAMS COULD NOT BE COPIED HERE. IM ATTACHING THE MAIN ASSIGNMENT AND A TUTORIAL ALSO. 2002ICT / 7003ICT Assignment Semester 1, 2010 Conceptual Modelling: Using the Enhanced Entity Relationship Data Model 150 points worth 15 marks (which is 15% of all marks in the course) Submit your assignment to the Library Assignment Handling Services. Date / time due: Wednesday 30th April 2010 3.00pm. Submit your assignment typed. The ER schema may be neatly drawn by hand or using a program such as MS Powerpoint. Staple. Fill in the attached coversheet to your assignment. SIGN IT. Remember the definition and risks of plagiarism. Make sure you work independently and submit your own work. The lecturer / convener reserves the right to interview you in which case you will have to explain how you solved the assignment. If you are selected for an interview then your mark becomes final only after the interview has been completed. Section I. (21 Points) Consider the conceptual schema in Figure 1. Please state in English (in the style of Section III) exactly 7 (seven) relevant facts captured in the schema. (If you supply more then seven facts, only the first seven will be marked.) Try to be as precise as possible. You must not use the words 'entity', 'relationship', 'attribute', 'cardinality', ‘weak’, 'participation', etc or abbreviations in your description (remember that your intended audience is not familiar with the ER model). Not more than one fact should be used to describe the attributes of one entity. Start each fact on a separate line. Please number your answers. Correct facts attract +3 points, major errors attracts -1 (negative) point, facts with minor errors 1, or 0 points depending on whether the error affects the ability of the reader to correctly interpret what you stated. Grammatically incorrect expression of facts (or misspelled in English) will be marked as incorrect. Remember what you have leant in Technical Writing in first year. Be careful with correct use of plurals, possessives, definite and indefinite articles (the, a, an). The use of colloquialisms is not acceptable. A good guide to common mistakes can be found at: [login to view URL] Section II. (49 Points) Map ER Schema to Relational schema Map the ER schema of Figure 1 to a relational schema according to the algorithms presented in the lectures. (Do not forget to correctly indicate all candidate keys and the primary key and foreign key constraints.) Section III. (80 Points) Develop a conceptual schema of a Universe of Discourse using the Entity Relationship Data Model Below is an English text that describes a possible Universe of Discourse. The text has been created in an interview with user representatives. You are asked to draw an ER schema that faithfully represents the facts captured in the English text. Please submit your ER diagram in a neat readable format, on an A4 sheet. Any additional semantic integrity constraints that you may find should be written on a separate sheet. (If you need to make an assumption you may do so; write your assumptions on a separate sheet.) Although there are other equivalent notations for ER, in order to be able to uniformly assess your work it is requested that you use the graphic notation taught in the lectures. 1. A business head office has a unique name, an address and may have several phone numbers. 2. The head office must have at least one, but may have several branches; each is located at a different address (unique for the head office of the business). The e-mail address of each branch is also stored in the database. 3. Branches may keep spare parts, in which case the stock level is also recorded. Spare parts have a recommended retail price (RRP) and a code unique for the factory that produced them. The date of production of each spare part is also stored. The names and locations of factories are unique as a whole. 4. Each branch employee has a unique ID and works in a branch. A salary, name and a preferred phone number are also recorded for branch employees. Branch employees may supervise several other branch employees but may only have one supervisor at most. 5. Employees can only be either service employees or checkout employees. Checkout employees have a checkout number stored while service employees have a department number 6. Service employees may order parts. If they do, the order quantity and date are stored. (note that service employees may only place one order for the same part in one day). 7. Parts may be used in cars. The make, model, engine size and transmission type are together used to uniquely identify a car type in order to find a matching spare part. 3 Checklist (pls. tick): Question 1: • You have submitted seven correct statements about Figure 1. Question 2: • You have submitted a relational schema, and: • You have marked all primary key and foreign key constraints. • You have correctly represented every attribute of each entity and relation. • You have chosen a correct way to map every entity, relation and attribute. Question 3. • You have recognised every entity (and represented it as an entity or as an attribute as appropriate). • You have recognised every weak entity. • You have correctly recognised generalisation hierarchies. • All relations are recognised and represented, or you have given suitable alternative representation of the corresponding facts. • Identifying relationships recognised and represented. • Correct primary keys represented for each entity participation and cardinality constraints are represented for each relationship (either of the two cardinality notations taught in the lectures is acceptable, as long as no ambiguity arises). • Partial keys are correctly represented for each weak entity. • Correct representation of complex and/or multi-valued attributes was given • Any necessary assumptions are written on a separate sheet. I.e., you have listed all facts that you needed to complete your diagram, but the text did not contain the information (or the text was ambiguous).