CSG1207D Systems and Database Design
(Database Analysis & Design)
Learning Outcomes (Why are you doing this assignment?)
This assignment addresses the following learning outcomes from the Unit Outline:
1. Use Data Analysis to design a Database (Task 1);
3. Implement a Database design using a Database Management system (DBMS), and
to construct complex queries upon it (Task 2);
General Assignment Information
Your first task is to design a database by analysing the requirements for the scenario detailed on the following pages. State any assumptions/notes you have made regarding your database design at the beginning of the database design document. Do not make any assumptions/notes that change the structure of the scenario, as this may make Task 2 of the assignment difficult. Only make assumptions/notes that influence your database design. If you are unsure about an assumption/note you wish to make, ask your lecturer/tutor.
Once you have identified the entities, attributes and relationships of the scenario in sufficient depth, you are required to create a logical ER diagram and a physical ER diagram to depict your database. Adhere to the distinctions between logical and physical ER diagrams covered in lectures. Show the Table Creation Order in your physical ERD.
Lastly, create a Table Instance Chart (TIC) for each entity in your data model. List your TICs in the appropriate table creation order that will need to be used to create the database. Include any additional information, if any, that may be needed to implement the database. Remember, the TICs should contain all the information needed to implement your database. Download the ‘Sample Table Instance Charts for Assignment Task 1’ from Week 0 in Moodle.
Your complete database design should consist of a list of assumptions/notes, logical and physical ER diagrams and TICs. This should be in the form of a single word-processed document. Include an ECC Assignment Coversheet on the first page of this document. Be sure to include details of both team
members if appropriate. You must also include the Marking Guide and the Work Allocation Plan (shown at the end of this document) as part of your submission.
You must use www.gliffy.com (or any other modelling tool you are familiar with) to draw your ERDs. Show Primary and Foreign Keys as such: CustomerID (PK) and CustID (FK). Do not underline as it is difficult to see and it is even harder when you have an attribute that is a PK as well as an FK. Instead of underlining use this format: OrderID (PK, FK), ProductID (PK, FK).
Scenario Details
You are required to design and create a database for Books-R-Us, a small general book store that sells books to the public. It would like to create a database to store information about books it sells and orders placed by customers. The following is a description of the entities of interest to the company and a list of attributes.
Please note that the attributes listed below are the minimum requested by the users. As an analyst you may add any detail you think is necessary for the design to work well and to support the business requirements. These will be your Notes.
Customers
The firm stores the following details about their customers:
Orders
The firm stores the following details about orders:
The shipping date must be validated against the order date; it may be the same as the order date or after, but not before the order date.
The company may apply one, or none, of the following discounts at the time of purchase:
Orders may be received in the following ways, and with the following data requirements:
Order type | Data to be stored |
Date received, Clerk who processed the order | |
Web | Email address, Reply method |
Phone | Clerk who took the call, Call start time, Customer’s phone number |
Books
The firm stores the following details about books:
The book categories used by the company are as follows:
The cost price must not exceed the wholesale price, which in turn must not exceed the recommended retail price.
Authors
The company stores the following details about the authors of the books they sell:
Publishers
The company stores the following details about the publishers of the books they sell:
General Information and Guidelines
The information above describes all of the entities and relationships required in the database design. Some details, such as the cardinality of some relationships, have been omitted. It is up to you to make and state any assumptions/notes you need in order to complete the database design. If you are uncertain about any part of the scenario described above, seek clarification from your lecturer.
It is strongly recommended that, where appropriate, you give each entity an auto-incrementing integer attribute as a primary key. Be sure to specify appropriate data types (and lengths, where applicable) for all columns in your Table Instance Charts. For example, while hourly pay rates are shown with a dollar sign in the table above, they should be stored in some kind of numeric form to allow mathematical operations to be performed on them.
Read the scenario details several times to ensure that your database design incorporates all the elements described. If you are uncertain about any part of the scenario described above, seek clarification from your lecturer.
Assumptions:
Logical ERD Diagram
Physical ERD Diagram
Table Instance Charts
Column Name | Key Type | Not Null | PK Table | PK Column | Data Type | Length | Domain Values | Default Value | Validation | Update/ Delete Rule | Meaning/Example |
CustomerID | PK | NN | - | - | INTEGER | Start at 0, increment by 1 | Next sequential value | - | - | A unique number to identify every customer. | |
FirstName | - | NN | - | - | VARCHAR | 25 | - | - | - | - | First Name |
LastName | - | NN | - | - | VARCHAR | 25 | - | - | - | - | Only last name. Is not included in name field. This is useful for some search. |
Gender | - | NN | - | - | VARCHAR | 1 | - | M | M or F | - | M for Male, F for Female |
ContactNum | - | NN | - | - | VARCHAR | 10 | - | - | - | - | Contact Number (10 digit number) |
EmailAddr | - | NN | - | - | VARCHAR | 45 | - | - | - | - | Email Address : @ symbol compulsory |
Column Name | Key Type | Not Null | PK Table | PK Column | Data Type | Length | Domain Values | Default Value | Validation | Update/ Delete Rule | Meaning/Example |
BookTypeID | PK | NN | - | - | INTEGER | Start at 0, increment by 1 | Next sequential value | - | - | A unique number to identify every book type. | |
BookTypeName | - | NN | - | - | VARCHAR | 30 | - | - | - | - | Book Type Name |
Column Name | Key Type | Not Null | PK Table | PK Column | Data Type | Length | Domain Values | Default Value | Validation | Update/ Delete Rule | Meaning/Example |
DiscountTypeID | PK | NN | - | - | INTEGER | Start at 0, increment by 1 | Next sequential value | - | - | A unique number to identify every discount type. | |
DiscountTypeName | - | NN | - | - | VARCHAR | 30 | - | - | - | - | Discount Type Name |
Discount | - | NN | - | - | INTEGER | 0 | >=0 | - | Discount Percentage value |
Column Name | Key Type | Not Null | PK Table | PK Column | Data Type | Length | Domain Values | Default Value | Validation | Update/ Delete Rule | Meaning/Example |
OrderTypeID | PK | NN | - | - | INTEGER | Start at 0, increment by 1 | Next sequential value | - | - | A unique number to identify every order type. | |
OrderTypeName | - | NN | - | - | VARCHAR | 6 | - | - | - | - | Order Type Name |
TypeDescription | - | NN | - | - | VARCHAR | 45 | - | - | - | - | Order Type Description |
Column Name | Key Type | Not Null | PK Table | PK Column | Data Type | Length | Domain Values | Default Value | Validation | Update/ Delete Rule | Meaning/Example |
AuthorID | PK | NN | - | - | INTEGER | Start at 0, increment by 1 | Next sequential value | - | - | A unique number to identify every author. | |
AuthFirstName | - | NN | - | - | VARCHAR | 25 | - | - | - | - | Author First Name |
AuthLastName | - | NN | - | - | VARCHAR | 25 | - | - | - | - | Author Last Name |
AuthContactNum | - | NN | - | - | VARCHAR | 10 | - | - | - | - | Author Contact Number |
AuthEmailAddr | - | NN | - | - | VARCHAR | 45 | - | - | - | - | Author Email Address |
Column Name | Key Type | Not Null | PK Table | PK Column | Data Type | Length | Domain Values | Default Value | Validation | Update/ Delete Rule | Meaning/Example |
PublisherID | PK | NN | - | - | INTEGER | Start at 0, increment by 1 | Next sequential value | - | - | A unique number to identify every publisher. | |
CompanyName | - | NN | - | - | VARCHAR | 30 | - | - | - | - | Company Name |
ContactPerson | - | NN | - | - | VARCHAR | 30 | - | - | - | - | Contact Person |
ContactDetails | - | - | - | - | VARCHAR | 45 | - | - | - | - | Contact Details |
EmailAddress | - | NN | - | - | VARCHAR | 45 | - | - | - | - | Email Address |
Column Name | Key Type | Not Null | PK Table | PK Column | Data Type | Length | Domain Values | Default Value | Validation | Update/ Delete Rule | Meaning/Example |
ISBN | PK | NN | - | - | VARCHAR | 15 | - | - | - | - | A unique number to identify every Book ISBN. |
Title | - | NN | - | - | VARCHAR | 30 | - | - | - | - | Book Title |
Edition | - | NN | - | - | VARCHAR | 3 | - | - | - | - | Book Edition Details |
PublisherID | - | NN | - | - | INTEGER | - | - | - | RI | OU: RESTRICT OD: RESTRICT | - |
DatePublished | - | NN | - | - | DATE | - | - | - | - | - | Date Published dd-mm-yyyy format |
CostPrice | - | NN | - | - | FLOAT | - | - | - | - | - | Cost Price |
WholeSalePrice | - | NN | - | - | FLOAT | - | - | - | - | - | Whole Sales Price |
RecommPrice | - | NN | - | - | FLOAT | - | - | - | - | - | Recommended Price |
BookTypeID | - | NN | - | - | INTEGER- | - | - | RI | OU: RESTRICT OD: RESTRICT | - | |
IsSequel | - | NN | - | - | VARCHAR | 1 | - | - | Y or N | Is Sequel? |
Column Name | Key Type | Not Null | PK Table | PK Column | Data Type | Length | Domain Values | Default Value | Validation | Update/ Delete Rule | Meaning/Example |
OrderID | PK | NN | - | - | INTEGER | Start at 0, increment by 1 | Next sequential value | - | - | A unique number to identify every order. | |
OrderDate | - | NN | - | - | Date | - | - | - | - | - | Ordered Date dd-mm-yyyy format |
ShipppedDate | - | - | - | - | Date | - | - | - | - | - | Shipped Date dd-mm-yyyy format |
DeliveryAddr | - | NN | - | - | VARCHAR | 60 | - | - | - | - | Delivery Address |
CustomerID | - | NN | - | - | VARCHAR | 6 | - | - | RI | OU: RESTRICT OD: RESTRICT | - |
OrderTypeID | - | NN | - | - | INTEGER | - | - | - | RI | OU: RESTRICT OD: RESTRICT | - |
DiscountTypeID | - | NN | - | - | INTEGER | - | - | - | RI | OU: RESTRICT OD: RESTRICT | - |
Column Name | Key Type | Not Null | PK Table | PK Column | Data Type | Length | Domain Values | Default Value | Validation | Update/ Delete Rule | Meaning/Example |
ISBN | PK, FK | NN | - | - | INTEGER | - | - | RI | OU: RESTRICT OD: RESTRICT | Composite Key | |
AuthorID | PK, FK | NN | - | - | INTEGER | - | - | - | RI | OU: RESTRICT OD: RESTRICT | Composite Key |
Royalty | - | NN | - | - | FLOAT | - | - | - | - | - | Royalty Percentage |
Column Name | Key Type | Not Null | PK Table | PK Column | Data Type | Length | Domain Values | Default Value | Validation | Update/ Delete Rule | Meaning/Example |
ISBN | PK, FK | NN | - | - | INTEGER | - | - | RI | OU: RESTRICT OD: RESTRICT | Composite Key | |
OrderID | PK, FK | NN | - | - | INTEGER | - | - | - | RI | OU: RESTRICT OD: RESTRICT | Composite Key |
Quantity | - | NN | - | - | INTEGER | - | - | - | - | - | Number of Books |
PriceSold | - | NN | FLOAT | - | - | - | - | - | Sold Price |