MSIT5210-Written Assignement 3
docx
keyboard_arrow_up
School
University of the People *
*We aren’t endorsed by this school
Course
5210
Subject
Information Systems
Date
Nov 24, 2024
Type
docx
Pages
9
Uploaded by UltraChinchilla1269
Written Assignment Unit 3
Database Systems Development and Implementation Plan
MSIT 5210-01 Databases
Dr. Shuchi Dhir
Abstract
There will be an Entity-Relationship (ER) diagram included in this paper as an overview of the Entity-Relationship Model that HNE Tech Company will use in the near future as part of this project. In the ER Diagram, the HNE design's tables, fields, relationships, and cardinality constraints are represented pictorial. HNE users can view how the tables will capture data, the different data types and how the related tables are connected to understand how they will capture
data.
Entity-Relationship Model
In database design, the Entity Relationship Model (ER Model) is a graphical framework that defines the relationships between a software system's components and represents the relationships between those components. In real life, an ER model is used to represent objects in the real world.
In the real world, an entity is a thing or object that can be distinguished from the surrounding environment in some way. For instance, each employee of an organization is considered an entity. Following are some of the significant characteristics of entities (Peterson, 2022).
List your subjects of interest (tables) for your target organization. Add at least 6 tables.
Customers
: table stores the customer’s information, including first name, last name, phone, email, street, city, state, and zip code.
Products
: able stores the product’s information such as name, brand, category, model year, and list price.
Orders
: table stores the sales order’s header information, including customer, order status, order date, required date, and shipped date.
Order Details: table stores the order Unit price, product ID, quantity, and size of the order.
Suppliers
: table stores the supplier’s information, including the Name, address, and email of the suppliers.
Category
: tables store product category name and description.
Payments
: stores payments made by customers based on their accounts.
Staff
: table stores the essential information of staff, including first name, and last name. It also contains communication information such as email and phone.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Role
: table includes the staff role information. Each role has a staff name, and contact information such as phone and email.
Determine the business rules for your target organization and list them.
Payment methods or unique customer IDs should be linked to all non-cash purchases. If a
phone number or other customer information is provided during purchase or during return, this information should be added to the customer profile.
The same customer profile can be associated with multiple payment methods if they are validated.
When a customer purchases more than a certain amount of an item or category within a 4-week period, they should receive discounted offers (up to 20% off) for like-kind merchandise.
A discount offer should be provided to customers with a consistent purchase history of specific items who stop purchasing those items during the same time period.
It is recommended that customers who purchase items that may have accessories with higher margins receive offers for discounted purchases within one week of making the purchase.
An item's return entitlement must be able to be determined by combining it with a payment method, or by combining it with a phone number if a payment method has been associated with a phone number in the past.
It is essential to track the date of purchase in order to ensure that the return is valid only within the return period.
It is crucial to track the price at the time of purchase in order to provide an adjustment if the price decreases after purchase or if the item are purchased at a discount that is no longer available.
Include an Entity Relationship Diagram showing the relationships between the entities (tables) and connections between them in crow's-foot notation.
Include a word table that showcases your entities, attributes for each (with data types), relationships, and cardinality constraints.
Table Name
Filed
Data Type
Is required
Customer
CustomerId
Int
Yes
Customer Name
varchar
No
Customer Address
varchar
No
City
char
No
Postal Code
int
No
Phone
int
No
Product
Product Id
Int
Yes
Product Name
Varchar
No
Product Type
Varchar
No
Price
Float
No
Manufacturer Varchar
No
Category_Id
Int
Yes
Order
OrderID
Int
Yes
CustomerId
Int
Yes
Order_Type
Varchar
No
Order_Status
Varchar
No
Ordere_Date
Date
No
Price
Float
No
Order_State
Varchar
No
Ordered_Detail
Order_Detail_Id
Int
Yes
Unit_Price
Float
No
Product_Id
int
Yes
Quantity
int
No
Size
int
No
Order_Id
Int
Yes
Bill_Number
Int
No
Supplier
Supplier_ID
Int
Yes
Name
VarChar
No
Address
VarChar
No
Phone
int
No
Email
VarChar
No
Category
Category_ID
int
Yes
Category_name
VarChar
No
Desciption
VarChar
No
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Payment
Bill_number
int
Yes
Payment_Type
VarChar
No
Other_Detail
VarChar
No
Staff
Staff_Id
Int
Yes
First_Name
VarChar
No
Last_Name
VarChar
No
Address
VarChar
No
Phone
Int
No
Email
VarChar
No
Role_ID
Int
Yes
Role
Role_Id
Int
Yes
Role_Name
VarChar
No
Description
VarChar
No
Ensure that the data in your model is in at least 3rd Normal Form (3NF).
As a database principle that builds upon the principles of database normalization provided by the
first normal form (1NF) and the second normal form (2NF), the third normal form (3NF) supports the integrity of data as a database principle. In addition to improving database processing, 3NF also reduces storage costs in order to maximize efficiency (
Chapple, 2022). With the removal of the additional dependent column to another table, and the reference of it by the use of a foreign key, all the tables have now become compliant to being on their 3NF as well.
Provide a narrative of how this design fulfills the mission and/or goals of your target organization.
This database design system serves the purpose of providing a database system that can be used by the HNE company that provides IT services as well as selling computer hardware, like storage, motherboards, RAM, video cards, and CPUs, depending on the customer's needs.
In addition to maintaining product information such as name, description, standard cost, list price, and product line, the company also manages inventory information for all products, including warehouses where products are available. Having warehouses in both North America and Canada, the company operates in both countries.
In addition to the company storing all information about the customers, including their names, addresses, and emails, the company also provides a contact person for each
customer who has detailed information about the customer including name, address, and phone. In the event that a customer issues a purchase order, an order is automatically created in the database in the form of a sales order with the pending status. As soon as the company ships the order, the status becomes
shipped. In the event that the customer cancels the order, the status becomes canceled.
Apart from the sales information, some basic information about the employees is also recorded, including their name, their e-mail, and their phone numbers, along with their job titles, the managers, and the dates of their employment.
References
Peterson, R. (2022). What is ER Modeling? Learn with Example
. Guru99. Retrieved 19 September 2022, from https://www.guru99.com/er-modeling.html
.
Chapple, M. (2022). How Third Normal Form (3NF) Helps Ensure Data Accuracy. Retrieved 21 September 2022, from https://www.lifewire.com/normalizing-your-database-third-1019726
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help