CBEC 1404 Certificate in Technology Databases 1
CBEC 1404 – Databases I (Introduction to Databases)
Assignment 1 – Conceptual Databases Design
Task 1: Conceptual Database Design
Given the following entity-relationship diagram, reproduce the database schema to reflect the
removal of the many-many relationship.
Task 1: Solution
DT036-1 Page 1 of 3 ©Dumitru Afanasiev 2010
CBEC 1404 Certificate in Technology Databases 1
Task 2: Conceptual Database Design
A publishing company produces computing books on various subjects. The books are
written by authors who specialize in one particular subject. The company employs editors
who, not necessarily being specialists in a particular area, each take sole responsibility for
editing one or more publications. A publication covers essentially one of the specialist
subjects and is normally written by a single author. When writing a particular book, each
author works with one editor, but may submit another work for publication to be
supervised by other editors. To improve their competitiveness, the company tries to
employ a variety of authors, more than one author being a specialist in a particular
subject.
Carry out the following:
Prepare a conceptual model, in the form of an entity-relationship diagram, for these
business requirements.
Your submission should include a “relationship matrix”
Task 2: Solution
DT036-1 Page 2 of 3 ©Dumitru Afanasiev 2010
CBEC 1404 Certificate in Technology Databases 1
Task 3: Conceptual Database Design
Assume you have the following database schema:
Note: Key attributes are underlined.
GIRLSCOUTS(ScoutID, GSName, Troop#, Address, City, Phone, email, DOB)
TROOPS(Troop#, LeaderName, LeaderPhone, AgeGroup)
ORDERS(OrderID, ScoutID, CustomerID, DeliveryDate)
BISCUITS(OrderID, ScoutID, Biscuit, BoxedSold)
CUSTOMERS(CustomerID, CustName, CustAddress)
Assumptions:
• Biscuit is the type of Biscuit bought (for example, Ginger Snaps).
• All troops have at least one Girl Scout.
• It’s possible that a Girl Scout makes no biscuit sales.
• You only keep track of customers who have bought biscuits.
• A customer can place more than one order.
• The orderID refers to the order ID of a particular a girl scout. For example,
girl scouts 23 and 56 can both have an order ID 1.
Carry out the following:
• Reproduce a “relationship matrix” to illustrate each of the relationship
identified.
• Provide a brief description of each of the relationship identified in your
“relationship matrix”.
• Reproduce the corresponding fully attributed “key–based” entity-
relationship diagram.
• Provide a brief description of the cardinally illustrated in your entity-
relationship diagram.
Note: Do not add any new fields to the tables.
Task 3: Solution
DT036-1 Page 3 of 3 ©Dumitru Afanasiev 2010
CBEC 1404 Certificate in Technology Databases 1
DT036-1 Page 4 of 3 ©Dumitru Afanasiev 2010
CBEC 1404 Certificate in Technology Databases 1
DT036-1 Page 5 of 3 ©Dumitru Afanasiev 2010
CBEC 1404 Certificate in Technology Databases 1
DT036-1 Page 6 of 3 ©Dumitru Afanasiev 2010
CBEC 1404 Certificate in Technology Databases 1
DT036-1 Page 7 of 3 ©Dumitru Afanasiev 2010
CBEC 1404 Certificate in Technology Databases 1
DT036-1 Page 8 of 3 ©Dumitru Afanasiev 2010
