Download FPTshop Database System and more Schemes and Mind Maps Data Communication Systems and Computer Networks in PDF only on Docsity!
ASSIGNMENT 1 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date 16/02/2024^ Date Received 1st submission Re-submission Date 20/01/2024 Date Received 2nd submission Student Name Le Dinh Hoai Anh Student ID GCD Class Assessor name Phan Thanh Tra Student declaration I certify that the assignment submission is entirely my own work and I fully understand the consequences of plagiarism. I understand that making a false declaration is a form of malpractice. Student’s signature Anh Grading grid
P1 M1 D
Contents
- Chapter 1 - Statements of user and system requirements (P1)
- 1.1 Overview of the Problem
- 1.2 Requirements of the application
- Chapter 2 – Design the relational database system (P1)
- 2.1 Analyse the requirements....................................................................................................................................................
- 2.2 ERD
- 2.3 Review whether the database is normalized
- 2.4 Data validation for each table
- a) Device:
- b) Category:
- c) User:
- d) Order:
- e) Rating:
- f) Store:
- g) Staff:
- h) Supplier:
- 2.5 Wireframe of the application
- Table 1 Device
- Table 2 Category
- Table 3 User
- Table 4 Order
- Table 5 Rating
- Table 6 Store
- Table 7 Staff
- Table 8 Supplier
- Figure 1: ERD can display entities and relationships
- Figure 2 ERD can display tables, PK, FK
- Figure 3 Database diagram
- Figure 4 Device
- Figure 5 Order....................................................................................................................................................................................
- Figure 6 Rating
- Figure 7 Staff
- Figure 8 Store
- Figure 9 Supplier................................................................................................................................................................................
- Figure 10 User
- Figure 11: Category
storage, efficient retrieval, and a user-friendly interface. FPT Shop stands to benefit significantly from these advancements in data management and operational efficiency.
1.2 Requirements of the application
a) User requirements As a user, I want to have the ability to:
- Register with my phone number as an ID so that I can easily access and manage my account. This will provide users with a convenient and personalized experience, allowing them to track their orders and access their purchase history.
- Browse and search for available devices in the FPT Shop. This will enable me to make informed purchasing decisions by exploring the range of devices and their specifications.
- Place orders for devices online. This will provide a convenient and efficient way to purchase without visiting a physical store.
- Rate and comment on the devices I have bought. This will allow me to share my feedback and experiences with other users, helping them make informed decisions. As a shop manager, I want to be able to:
- Manage and update the inventory of devices in my store. This will enable me to keep track of stock levels and ensure accurate product availability for customers.
- View and manage the orders placed by customers in my store. This will help me process orders effectively and provide timely service to customers. As a director board member, I want to be able to:
- View and analyze data from all shops. This will provide insights into sales performance, customer preferences, and overall business trends, enabling informed decision-making.
- Generate reports on sales, revenue, and other key metrics across all shops. This will facilitate data-driven decision-making and effective business strategy. b) System requirement User Registration:
- Users should be able to register with their phone numbers as unique identifiers.
- The system should validate and store user registration details securely. Device Management:
- The system should allow users to browse and search for available devices.
- Shop managers should be able to manage and update the inventory of devices in their respective stores.
- The system should track device stock levels and provide accurate product availability information. Order Management:
- Users should be able to place orders for devices online.
- Shop managers should have access to view and manage the orders placed in their stores.
- The system should facilitate order processing, tracking, and fulfillment. Rating:
- The system should store and display user ratings and comments for each device. Shop Management:
- Shop managers should have access to a dashboard or interface to manage their stores.
Device: DeviceID (Primary Key), Name, Brand, Model, Specs, Price, Stock, SuplierID(Foreign Key), StoreID(Foreign Key), CategoryID(Foreign Key). Category: CategoryID (Primary Key), Name. Order: OrderID (Primary Key), UserID (Foreign Key), DeviceID (Foreign Key), OrderDate, Quantity, Status. Rating: UserID (Foreign Key, Primary Key), DeviceID (Foreign Key, Primary Key), Rating, Comment, RatingDate. Store: StoreID (Primary Key), Name, Location, ContactDetail, OperatingHours. Staff: StaffID (Primary Key), StoreID (Foreign Key), Name, Position, ContactDetails. Suplier: SupplierID (Primary Key), Name, ContactDetails, Address.
2.2 ERD
Figure 1 : ERD can display entities and relationships
and includes information about that user's specific rating for that device. This allows each user to rate multiple devices, and each device can also receive reviews from multiple users.
- The Category table demonstrates a one-to-many relationship with the Device table, indicating that many devices can share the same category.
- The Store table can establish a many-to-many relationship with the Devices table, emphasizing that a store can offer multiple devices belonging to the same category, such as laptop store, computer store spreadsheets and other categories. This relationship reflects the association between information about the store and the devices it offers. This way, information about stores can be easily tracked and combined with devices belonging to the same category, creating a structured database that works and is easy to manage.
- The Supplier table showcases a one-to-many relationship with the Device table, indicating that one supplier can provide multiple devices.
- The Order table establishes many-to-one relationships with the User and Device tables, indicating that one user can place multiple orders and one device can be part of numerous orders.
- The Staff table establishes a many-to-one relationship with the Store table, elucidating that one store can employ multiple staff members.
2.3 Review whether the database is normalized
Prior to implementing the final changes to the database, there existed an attribute named TotalPrice in the Order table, aimed at calculating the total price through the formula: quantity * the price of the device. This design was identified as violating the principles of the Third Normal Form (3NF) due to its Transitive Functional Dependence. In adherence to normalization rules, specifically aiming for 3NF, the TotalPrice attribute was subsequently removed. The rationale behind this decision lies in the fact that the total price can be derived by performing a calculation based on the quantity of the order and the price of the device. Following the elimination of the TotalPrice attribute, the updated Order table is now represented as follows:
- Order: OrderID (Primary Key), UserID (Foreign Key), DeviceID (Foreign Key), OrderDate, Quantity, Status. It is noteworthy that the other tables within the database remain unaltered, as they adhere to normalization rules and maintain conformity with the desired normalization form.
2.4 Data validation for each table
a) Device:
- DeviceID: Ensure it is a unique identifier for each device.
- Name: Validate that it is not empty and within an appropriate length limit.
- Brand, Model, Specs: Ensure they are not empty and within appropriate length limits.
- Price: Validate that it is a non-negative value.
- Stock: Ensure it is a non-negative integer.
- SupplierID, StoreID, CategoryID: Ensure they reference valid foreign keys in the respective tables. b) Category:
- CategoryID: Ensure it is a unique identifier for each category.
- Name: Validate that it is not empty and within an appropriate length limit c) User: UserID: Ensure it is a unique identifier for each user.
- PhoneNum: Ensure it is not empty and within an appropriate length limit.
- Name: Validate that it is not empty and within an appropriate length limit.
- Address: Ensure it is a unique identifier for each user, it is not empty and within an appropriate length limit.
- Email: Allow null values but validate it to be within an appropriate length limit.
- Password: Ensure it is not empty and within an appropriate length limit. d) Order:
- OrderID: Ensure it is a unique identifier for each order.
- UserID, DeviceID: Validate that they reference valid foreign keys in the respective tables.
- OrderDate: Validate that it is a valid date format.
- Quantity: Ensure it is a positive integer.
- Status: Validate that it has predefined values. e) Rating:
- UserID, DeviceID: Validate that they reference valid foreign keys in the respective tables.
- Rating: Ensure it falls within an acceptable range (e.g., 1-5).
- Comment: Validate that it is not empty and within appropriate length limits.
Model Nvarchar( 50 ) No Specs Nvarchar( 2 00) No Price int No > Stock int No >= SupplierID Nvarchar(10) No FK (Supplier.SupplierID) StoreID Nvarchar(10) No FK (Store.StoreID) CategoryID Nvarchar(10)^ No^ FK^ (Category.CategoryID) CREATE TABLE Device ( DeviceID INT NOT NULL IDENTITY( 1 , 1 ) PRIMARY KEY, Name NVARCHAR( 100 ) NOT NULL, Brand NVARCHAR( 50 ) NOT NULL, Model NVARCHAR( 50 ) NOT NULL, Specs NVARCHAR( 200 ) NOT NULL, Price INT NOT NULL CHECK (Price > 0 ), Stock INT NOT NULL CHECK (Stock >= 0 ), SupplierID INT NOT NULL, StoreID INT NOT NULL, CategoryID INT NOT NULL, FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID), FOREIGN KEY (StoreID) REFERENCES Store(StoreID), FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID) ); Category table : Category table stores information about different device categories. CategoryID must be the primary key and auto increments. The Name column must not be null. Table 2 Category Column name Data type Allow null Constraint CategoryID int No PK, Auto Increment Name nvarchar(100) No
CREATE TABLE Category ( CategoryID INT NOT NULL IDENTITY( 1 , 1 ) PRIMARY KEY, Name NVARCHAR( 100 ) NOT NULL ); User table : The User table is designed to store information about users. It includes columns such as UserID, Name, PhoneNum, Address, Email, and Password. The UserID column serves as the primary key, ensuring a unique identifier for each user. The Name column stores the name of each user, while the PhoneNum and Address columns store their respective contact information. The Email column allows for the storage of email addresses, which can be optional. The Password column stores the user's password. By utilizing these columns and their associated validations, the User table facilitates the storage and retrieval of user-related information, enabling effective user management within a database system. Table 3 User Column name Data type Allow null Constraint UserID nvarchar(20)^ No^ PK, Auto Increment Name nvarchar(100) No Address nvarchar(200) No Email nvarchar(100) Yes Password Nvarchat(20)^ Yes CREATE TABLE [User] ( UserID INT NOT NULL IDENTITY( 1 , 1 ) PRIMARY KEY, PhoneNumID NVARCHAR( 20 ) NOT NULL UNIQUE, Name NVARCHAR( 100 ) NOT NULL, Address NVARCHAR( 200 ) NOT NULL, Email NVARCHAR( 100 ), Password NVARCHAR( 20 ) CHECK (LEN(Password) <= 20 ) );
DeviceID: Is a foreign key referencing the DeviceID column in the Device table. This links the review to the specific device the user reviewed. Rating: Is a tinyint data type column that can contain null values, and is constrained to be between 1 and 5. This ensures the rating value is within a reasonable range. Comment: Is a column of data type nvarchar(200) that stores user comments. This column contains a detailed description or user opinion about the device. RatingDate: Is a date column that does not accept null values, representing the date on which the rating was sent. This helps track when reviews are added. This Rating table allows linking between users, devices and their respective ratings and comments, while ensuring the validity of rating values within the specified range.. Table 5 Rating Column name Data type Allow null Constraint UserID int^ No^ FK^ (User.UserID) DeviceID int No FK (Device.DeviceID) Rating tinyint Yes Check Constraint (Rating >= 1 AND Rating <= 5) RatingDate date No CREATE TABLE Rating ( RatingID INT NOT NULL IDENTITY( 1 , 1 ) PRIMARY KEY, UserID INT NOT NULL, DeviceID INT NOT NULL, Rating TINYINT NOT NULL, RatingDate DATE NOT NULL DEFAULT GETDATE(), FOREIGN KEY (UserID) REFERENCES User, FOREIGN KEY (DeviceID) REFERENCES Device(DeviceID), CHECK (Rating >= 1 AND Rating <= 5 ) );
Store table : The Store table is used to store information about different stores. It consists of several columns: StoreID, Name, Location, ContactDetail, and OperatingHours. The StoreID column serves as the primary key and ensures a unique identifier for each store. The Name, Location, ContactDetail, and OperatingHours columns store the respective information for each store and are validated to ensure they are not empty and within appropriate length limits. The table allows for the storage and retrieval o f store- related data, facilitating management and organization of store information within a database system. Table 6 Store Column name Data type Allow null Constraint StoreID int No PK, Auto Increment Name nvarchar(100)^ No Location nvarchar(100) No ContactDetail nvarchar(100) No OperatingHours nvarchar(100) No CREATE TABLE Store ( StoreID INT NOT NULL IDENTITY( 1 , 1 ) PRIMARY KEY, Name NVARCHAR( 100 ) NOT NULL, Location NVARCHAR( 100 ) NOT NULL, ContactDetail NVARCHAR( 100 ) NOT NULL, OperatingHours NVARCHAR( 100 ) NOT NULL ); Staff table : The Staff table is used to store information about staff members working in different stores. It includes columns such as StaffID, StoreID, Name, Position, and ContactDetails. The StaffID column serves as the primary key to ensure a unique identifier for each staff member. The StoreID column is a foreign key that references a valid foreign key in the Store table, establishing a relationship between staff members and the stores they belong to. The Name column stores the name of each staff member, while the Position column holds their position or role within the organization. The ContactDetails column stores the contact information, such as phone number or email address, for each staff member. By utilizing these columns and their associated validations, the Staff table facilitates the storage and retrieval of staff-related information, enabling effective staff management within the context of different stores.
