COMPUTER BUSINESS

In computer business basically we are trying to sell computer products. So for that we have several tables to make our system more dynamic. The basic tables needed are, login table which contains the login details of the customer, user tables which contain the details of the customer who is registering into the system. According to the functions that we are providing, the number of tables and relationships between the tables are going to be defined.

The tables are named based on the data which is going to be stored into the table. For each table there will be a unique key called primary key to fetch the appropriate data from the tables. According to the forms and the flow of the system, we will provide the suitable field name and data type for each columns of tables.

This is a basic database structure given for the corresponding system. Here are the only basic activities to give an overview of our database.

Function: Computer Sales

In this function, the user is going to purchase a product through our system. So initially, the user wanted to view the item, which is there on the website, for that all the data is retrieved from the product table. If the user tries to buy a particular product, then that details will be stored in the sales table. We must fetch the user details form the user table to get the user information and stored along with other informations in the product tables. Finally, if the user is buying the item via online payment, the details must be stored in the payment tables.

Required tables:

Sales Table (To store the sales details like sales id, amount, purchaser and id)

Product Table (From this table we are fetching product details like specifications, quantity etc.)

User Table (From user table we are fetching the details of purchaser like name, phone etc.)

Payment Table (It store the payment details like amount, payment type etc.)

Business Rules

Sales can only be made to registered users: This rule means that the sales table should have a foreign key column that references the user table, to ensure that each sale is associated with a registered user.

A sale must be associated with a product: This rule means that the sales table should have a foreign key column that references the product table, to ensure that each sale is associated with a specific product.

A sale may or may not involve an online payment: This rule means that the payment table should have a nullable foreign key column that references the sales table, to allow for the possibility of sales that do not involve an online payment.

A product must have a unique name: This rule means that the product name column in the product table should be the primary key, to ensure that each product has a unique name.

Each sale, user, and payment should have a unique identifier: This rule means that the sales table should have a primary key column (sales id), the user table should have a primary key column (user id), and the payment table should have a primary key column (payment id), to ensure that each sale, user, and payment has a unique identifier.

Primary Keys

SalesID (sales table)

ProdName (Product table)

UserID (user table)

PaymentID (payment table)

Attributes

Sales table:

sales id (primary key): a unique identifier for each sale

amount: the total amount of the sale

purchaser (foreign key referencing user id in the user table): the user who made the purchase

product (foreign key referencing product name in the product table): the product that was purchased

Product table:

product name (primary key): a unique identifier for each product

specifications: details about the product, such as its size, features, or capabilities

quantity: the number of units of the product available for sale

User table:

user id (primary key): a unique identifier for each user

name: the user's name

phone: the user's phone number

email: the user's email address

Payment table:

payment id (primary key): a unique identifier for each payment

amount: the total amount of the payment

payment type: the method of payment, such as credit card, debit card, or online payment service

sale (nullable foreign key referencing sales id in the sales table): the sale that the payment is for (if applicable)

------------------------------------------------------------------------------------------------------------

Based off info above I need to answer these questions.

Instructions Use MS Visio to create relational data schemas and...

Instructions

Use MS Visio to create relational data schemas and data dependency diagrams for all entities (tables). Start out with listing all data attributes in 1NF, then label partial dependencies and potential transitive dependencies. Refer to guided practice 3.2.

Next, break down all partial dependencies into their own entities (tables) normalizing to 2NF. Note: You can create new pages in Visio using the plus sign at the bottom of the page.

Lastly, break down any transitive dependencies into their respective entities, normalizing to 3NF. Keep in mind that it is possible not to have any transitive dependencies and therefore your diagrams and schemas in 2NF could also be in 3NF. Be sure that each entity (table) is in 3NF and identifies primary and foreign keys.

Create a ER diagram of your tables in 3NF. Include all minimum and maximum cardinalities with the crow's feet notation.

Save your .vsdx file.