Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

The proprietors of BigM have approached you and asked if you could design a database to help them manage their business. The management has commissioned you (in your capacity as a Database Management System consultant) to analyse, design and develop an appropriate conceptual data model and relational database schema, based on the following information gathered about the current business activities.

  • BigM operates stores in many cities in Australia. Stores are referenced by store number. BigM also keeps store name, phone, fax, email for each store. Each store has a postal address containing street address, city, state and postcode.
  • Each store has a number of departments, for example, finance, accounts, sales, customer service etc. For each department, there is a department ID, department name, phone and e-mail addresss.
  • Each department has a number of For each employee, BigM keeps a record of their employee ID, first name, last name, phone, date of birth, start date of his/her work and tax file number.
  • BigM also stores postal and residential addresses of their For each address they need to store street address, city, state and postcode.
  • For each employee, his or her job type (for example, accountant, sales, customer service etc.), and annual salary is recorded.
  • Each store is managed by an employee as a store manager and each department is supervised by an employee. The department supervisor is also the supervisor for all the staffs within that department.
  • Each store may be assigned a supervising store where all training, payroll, server application and help desk are located.
  • Each supervising store generates pay slips for all staffs (in this store and other stores being supervised) on a fortnightly For each pay slip, the store records a pay ID, supervising store ID, employee ID, pay date, number of hours and the gross payment.
  • BigM sales different products like CDs, cloths, computers etc. For each product it stores product number, product description, brand, toys, product size and
  • An inventory of the number of each particular product in each store is BigM keeps track of the quantity of each product that is on order, as well as the number currently available in each store.
  • Customer may place orders in the store. Customer details are always taken at each A customer is referenced by a customer number, customer first & last names, phone number and postal address, if available. For each address they need to store street address, city, state and postcode.
  • A customer may order more than one product at a time, and they may order multiple copies of the same product. BigM also records the date a product arrives and the date when it is picked up by the customer. Note that these dates may be different for each product.

BigM understands that they may not have provided you with sufficient information. If you need to make assumptions about their organisation please ensure that you record them.

From the BigM’s business requirements specified above, prepare a document according to the followings:

1. Use the supplied template for your Assignment

2. An appropriate title page that includes an acknowledgement of all students you have spoken to about the assignment. 

3. A table of contents and page

4. An ERD using Crow’s Foot notation. The diagram should include:

a) all entities, attributes, and relationships (including names) ;

b) primary keys (underlined) and foreign keys (italic) identified;

c) cardinality and modality (optional / mandatory) symbols; and

d) assumptions you have made, e.g., how you arrived at the cardinality and/or participation for those not mentioned or clear in the business description,

5. Normalisation of the relations which identifies:

a) dependency diagram for each relation

b) the level of Normalisation achieved for each relation

c) the reasons for any relation that is NOT maintained in

6. Relational database schema that translate your ERD and include:

a) relation (table) names,

b) attribute (column ) names and data types (as required by XAMPP or UwAmp),

c) length of each field & its description (e.g., if it is a primary key, a foreign key referring to another table or format, etc.)

d) primary and foreign keys identified;

7. A bibliography, containing all resources used to complete the assignment. If no resources have been used please indicate this appropriately.

                                           

                                                                                                                 (Source: Created by Author)

Assumptions: The customer will make order from the website. A bridge table has been used for tracking the product quantity for each order. The postal and residential address will refer to the same entity. An employee acting as the supervisor of the store will not work in any department. The pay slip will be provided to the employees by supervising store under which they work. The inventory will be used to track the stock the availability of the products. The inventory entity also specifies which store has which products.

1st Normal Form: In order to be in the first normal form, the attributes are required have only atomic values and a primary key for each table. As shown the figure 1 ERD, all the tables have at least one primary key and all the attributes are atomic. Therefore, the database tables are perfectly in first normal form.

2nd Normal Form: The second normal form states that non-key attributes should be depended on key attributes.

Table name: Customer

Entity

Data Type

Size

Key

Reference Table

customerNumber

Int

8

Primary Key

None

firstName

Varchar

40

None

None

lastName

Varchar

40

None

None

phone

Int

10

None

None

addressID

Int

8

Primary Key

None

street

Varchar

40

None

None

city

Varchar

40

None

None

state

Varchar

40

None

None

postCode

Int

8

None

None

In the above table, the street, city, state and postcode are depended on the addressID. Therefore, the table has partial dependency. To remove the partial dependency, the table is divided into two separate tables named customer and address.

Table name: Customer

Entity

Data Type

Size

Key

Reference Table

customerNumber

Int

8

Primary Key

None

firstName

Varchar

40

None

None

lastName

Varchar

40

None

None

phone

Int

10

None

None

postalAddress

Int

8

Foreign

Address (addressID)

Table name: Address

Entity

Data Type

Size

Key

Reference Table

addressID

Int

8

Primary Key

None

street

Varchar

40

None

None

city

Varchar

40

None

None

state

Varchar

40

None

None

postCode

Int

8

None

None

3rd Normal Form: The database must be in second normal form and it does not have any transitive dependency among the attributes.

Table name: Store

Entity

Data Type

Size

Key

Reference Table

storeNumber

Int

8

Primary Key

None

storeName

Varchar

40

None

None

phone

Varchar

40

None

None

fax

Int

15

None

None

email

Varchar

40

None

None

postalAddress

Int

9

Foreign

Address (addressID)

manager

Int

9

Foreign

Employee (employeeID)

supervisinStore

Int

8

Foreign

Store (storeNumber)

departmentID

Int

8

Primary Key

None

supervisor

Int

8

Foreign

Employee (employeeID)

departmentName

Varchar

40

None

None

phone

Int

10

None

None

emailAddress

Varchar

40

Foreign

Address (addressID)

The storeID determines the departmentID detrrmines the departmentName. But the departmentID does determines storeID. Therefore, the table has transitive dependency. To remove the dependency, the table has been separated into two table named store and department.

Table name: Store

Entity

Data Type

Size

Key

Reference Table

storeNumber

Int

8

Primary Key

None

storeName

Varchar

40

None

None

phone

Varchar

40

None

None

fax

Int

15

None

None

email

Varchar

40

None

None

postalAddress

Int

9

Foreign

Address (addressID)

manager

Int

9

Foreign

Employee (employeeID)

supervisinStore

Int

8

Foreign

Store (storeNumber)

Table name: Department

Entity

Data Type

Size

Key

Reference Table

departmentID

Int

8

Primary Key

None

store

Int

8

Foreign

Store (storeNumber)

supervisor

Int

8

Foreign

Employee (employeeID)

departmentName

Varchar

40

None

None

phone

Int

10

None

None

emailAddress

Varchar

40

Foreign

Address (addressID)

Table name: Customer

Entity

Data Type

Size

Key

Reference Table

customerNumber

Int

8

Primary Key

None

firstName

Varchar

40

None

None

lastName

Varchar

40

None

None

phone

Int

10

None

None

postalAddress

Int

8

Foreign

Address (addressID)

Table name: Address

Entity

Data Type

Size

Key

Reference Table

addressID

Int

8

Primary Key

None

street

Varchar

40

None

None

city

Varchar

40

None

None

state

Varchar

40

None

None

postCode

Int

8

None

None

Table name: Employee

Entity

Data Type

Size

Key

Reference Table

employeeID

Int

8

Primary Key

None

firstName

Varchar

40

None

None

lastName

Varchar

40

None

None

phone

Int

10

None

None

dateOfBirth

Int

8

None

None

workStartDate

Date

None

None

taxFileNumber

Int

11

None

None

postalAddress

Int

8

Foreign

Address (addressID)

residentialAddress

Int

8

Foreign

Address (addressID)

jobType

Varchar

40

None

None

annualSallary

Varchar

40

None

None

Table name: Store

Entity

Data Type

Size

Key

Reference Table

storeNumber

Int

8

Primary Key

None

storeName

Varchar

40

None

None

phone

Varchar

40

None

None

fax

Int

15

None

None

email

Varchar

40

None

None

postalAddress

Int

9

Foreign

Address (addressID)

manager

Int

9

Foreign

Employee (employeeID)

supervisinStore

Int

8

Foreign

Store (storeNumber)

Table name: Department

Entity

Data Type

Size

Key

Reference Table

departmentID

Int

8

Primary Key

None

store

Int

8

Foreign

Store (storeNumber)

supervisor

Int

8

Foreign

Employee (employeeID)

departmentName

Varchar

40

None

None

phone

Int

10

None

None

emailAddress

Varchar

40

Foreign

Address (addressID)

Table name: PaySlip

Entity

Data Type

Size

Key

Reference Table

payID

Int

8

Primary Key

None

supervisingStore

Int

8

Foreign

Store (storeNumber)

employee

Int

8

Foreign

Employee (employeeID)

payDate

Date

None

None

numberOfHours

Time

None

None

grossPayment

Number

10,2

None

None

Table name: Inventory

Entity

Data Type

Size

Key

Reference Table

store

Int

8

Primary Key, Foreign

Store (storeNumber)

product

Int

8

Foreign

Product (productNumber)

availableQuantity

Int

8

None

None

Table name: Product

Entity

Data Type

Size

Key

Reference Table

productNumber

Int

8

Primary Key

None

productDescription

Varchar

200

None

None

brand

Varchar

40

None

None

tyos

Varchar

40

None

None

productSize

Int

8

None

None

price

Number

10,2

None

None

Table name: OrderProduct

Entity

Data Type

Size

Key

Reference Table

orderID

Int

8

Primary Key, Foreign

Order (orderID)

product

Int

8

Primary Key, Foreign

Product (productNumber)

quantity

Int

8

None

None

arrivalDate

Date

None

None

pickupDate

Date

None

None

Table name: OrderProduct

Entity

Data Type

Size

Key

Reference Table

orderID

Int

8

Primary Key

None

customer

Int

8

Primary Key

Customer (customerNumber)

date

Date

None

None

cost

Number

10,2

None

None

Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational database schema. International Journal of Database Theory and Application, 8(3), 15-26.

Letkowski, J. (2015). Doing database design with MySQL. Journal of Technology Research, 6, 1.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2020). Designing A Database For BigM: Conceptual Data Model, Relational Database Schema, And Essay.. Retrieved from https://myassignmenthelp.com/free-samples/1804ict-data-management/relational-database-schema.html.

"Designing A Database For BigM: Conceptual Data Model, Relational Database Schema, And Essay.." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/1804ict-data-management/relational-database-schema.html.

My Assignment Help (2020) Designing A Database For BigM: Conceptual Data Model, Relational Database Schema, And Essay. [Online]. Available from: https://myassignmenthelp.com/free-samples/1804ict-data-management/relational-database-schema.html
[Accessed 30 December 2024].

My Assignment Help. 'Designing A Database For BigM: Conceptual Data Model, Relational Database Schema, And Essay.' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/1804ict-data-management/relational-database-schema.html> accessed 30 December 2024.

My Assignment Help. Designing A Database For BigM: Conceptual Data Model, Relational Database Schema, And Essay. [Internet]. My Assignment Help. 2020 [cited 30 December 2024]. Available from: https://myassignmenthelp.com/free-samples/1804ict-data-management/relational-database-schema.html.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
close