Database To Store Information About Books

pages Pages: 4word Words: 890

Question :

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:

  • First name and last name
  • Gender
  • Contact numbers
  • Email Address


Orders

The firm stores the following details about orders:

  • An order ID
  • The date of the order
  • The date the order was shipped
  • Details about the customer who placed the order
  • Details about the books sold, such as quantity, price sold, etc.
  • Any discounts applied to the order

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:

  • Staff discount (5%)
  • Seniors discount (10%)
  • Loyalty discount (15%)

Orders may be received in the following ways, and with the following data requirements:

Order typeData to be stored
Mail Date received, Clerk who processed the order
WebEmail address, Reply method
PhoneClerk who took the call, Call start time, Customer’s phone number


Books

The firm stores the following details about books:

  • The ISBN
  • The title
  • The edition of the book
  • The publisher name
  • The date published
  • The cost price, the wholesale price, and the recommended price
  • The category
  • The sequel or prequel (if any) of a book
  • The author order (in the case of books with multiple authors)
  • The percentage royalty that must be paid to each author


The book categories used by the company are as follows:

  • Fiction
  • Non-fiction
  • Romance
  • History
  • Business

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:

  • First name and last name
  • Contact number
  • Email address

Publishers

The company stores the following details about the publishers of the books they sell:

  • Company name
  • Contact person and contact details
  • Email address of the publisher

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. 


Show More

Answer :

Assumptions:

  • Each Book has any one of the Book Type
  • Each Order may have any one of the Order type
  • Each order may have any one of the Discount Type
  • Each order may have one or more Books

Logical ERD Diagram

Logical ERD Diagram


Physical ERD Diagram

Physical ERD Diagram


Table Instance Charts

Table Instance Chart: CUSTOMERS


Column NameKey TypeNot NullPK TablePK ColumnData TypeLengthDomain ValuesDefault ValueValidation Update/ Delete RuleMeaning/Example
CustomerIDPKNN--INTEGERStart at 0, increment by 1Next sequential value--A unique number to identify every customer.
FirstName-NN--VARCHAR25----First Name
LastName-NN--VARCHAR25----Only last name. Is not included in name field. This is useful for some search.
Gender-NN--VARCHAR1-MM or F-M for Male, F for Female
ContactNum-NN--VARCHAR10----Contact Number (10 digit number)
EmailAddr-NN--VARCHAR45----Email Address : @ symbol compulsory

 



Table Instance Chart: BookType


Column NameKey TypeNot NullPK TablePK ColumnData TypeLengthDomain ValuesDefault ValueValidation Update/ Delete RuleMeaning/Example
BookTypeIDPKNN--INTEGERStart at 0, increment by 1Next sequential value--A unique number to identify every book type.
BookTypeName-NN--VARCHAR30----Book Type Name

 

Table Instance Chart: DiscountType


Column NameKey TypeNot NullPK TablePK ColumnData TypeLengthDomain ValuesDefault ValueValidation Update/ Delete RuleMeaning/Example
DiscountTypeIDPKNN--INTEGERStart at 0, increment by 1Next sequential value--A unique number to identify every discount type.
DiscountTypeName-NN--VARCHAR30----Discount Type Name
Discount-NN--INTEGER

0>=0-Discount Percentage value




Table Instance Chart: OrderType


Column NameKey TypeNot NullPK TablePK ColumnData TypeLengthDomain ValuesDefault ValueValidation Update/ Delete RuleMeaning/Example
OrderTypeIDPKNN--INTEGERStart at 0, increment by 1Next sequential value--A unique number to identify every order type.
OrderTypeName-NN--VARCHAR6----Order Type Name
TypeDescription-NN--VARCHAR45----Order Type Description


Table Instance Chart: Authors


Column NameKey TypeNot NullPK TablePK ColumnData TypeLengthDomain ValuesDefault ValueValidation Update/ Delete RuleMeaning/Example
AuthorIDPKNN--INTEGERStart at 0, increment by 1Next sequential value--A unique number to identify every author.
AuthFirstName-NN--VARCHAR25----Author First Name
AuthLastName-NN--VARCHAR25----Author Last Name
AuthContactNum-NN--VARCHAR10----Author Contact Number
AuthEmailAddr-NN--VARCHAR45----Author Email Address



Table Instance Chart: Publisher


Column NameKey TypeNot NullPK TablePK ColumnData TypeLengthDomain ValuesDefault ValueValidation Update/ Delete RuleMeaning/Example
PublisherIDPKNN--INTEGERStart at 0, increment by 1Next sequential value--A unique number to identify every publisher.
CompanyName-NN--VARCHAR30----Company Name
ContactPerson-NN--VARCHAR30----Contact Person
ContactDetails----VARCHAR45----Contact Details
EmailAddress-NN--VARCHAR45----Email Address
Table Instance Chart: Books
Column NameKey TypeNot NullPK TablePK ColumnData TypeLengthDomain ValuesDefault ValueValidation Update/ Delete RuleMeaning/Example
ISBNPKNN--VARCHAR15----A unique number to identify every Book ISBN.
Title-NN--VARCHAR30----Book Title
Edition-NN--VARCHAR3----Book Edition Details
PublisherID-NN--INTEGER---RIOU: 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-
--RIOU: RESTRICT
OD: RESTRICT
-
IsSequel-NN--VARCHAR1--Y or N
Is Sequel?
Table Instance Chart: Orders


Column NameKey TypeNot NullPK TablePK ColumnData TypeLengthDomain ValuesDefault ValueValidation Update/ Delete RuleMeaning/Example
OrderIDPKNN--INTEGERStart at 0, increment by 1Next 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--VARCHAR60----Delivery Address
CustomerID-NN--VARCHAR6--RIOU: RESTRICT
OD: RESTRICT
-
OrderTypeID-NN--INTEGER---RIOU: RESTRICT
OD: RESTRICT
-
DiscountTypeID-NN--INTEGER---RIOU: RESTRICT
OD: RESTRICT
-


Table Instance Chart: BookAuthor


Column NameKey TypeNot NullPK TablePK ColumnData TypeLengthDomain ValuesDefault ValueValidation Update/ Delete RuleMeaning/Example
ISBNPK, FKNN--INTEGER--RIOU: RESTRICT
OD: RESTRICT
Composite Key
AuthorIDPK, FKNN--INTEGER---RIOU: RESTRICT
OD: RESTRICT
Composite Key
Royalty-NN--FLOAT-----Royalty Percentage



Table Instance Chart: BookAuthor


Column NameKey TypeNot NullPK TablePK ColumnData TypeLengthDomain ValuesDefault ValueValidation Update/ Delete RuleMeaning/Example
ISBNPK, FKNN--INTEGER--RIOU: RESTRICT
OD: RESTRICT
Composite Key
OrderIDPK, FKNN--INTEGER---RIOU: RESTRICT
OD: RESTRICT
Composite Key
Quantity-NN--INTEGER-----Number of Books
PriceSold-NN

FLOAT-----Sold Price