Database System for Sampath Food City (PVT) Ltd Assignment

Assignment Task

Sampath Food City (PVT) Ltd is a leading retail grocery shop located in Matugama, Sri Lanka with a 5000 of customer base. It sells several grocery items to the customers such as bakery, beverage, pharmacy, meat, seafood and etc. Currently, the shop uses an entirely manual system to handle day-to-day transactions of the shop. Therefore, several drawbacks and weaknesses are occurred such as inaccuracy, inefficiency, unreliability, and data redundancy. The top-level management of the shop has identified the need for a computer-based database system for handling day-to-day transactions of the shop. Spera Labs (PVT) Ltd is a leading software development company in Sri Lanka and the top–level management of Sampath Food City (PVT) Ltd has given a contract to Spera Labs (PVT) to develop a database system for Sampath Food City (PVT) Ltd.

Imagine that you are a software engineer at Spera Labs (PVT) Ltd and you are assigned to develop that database system for Sampath Food City (PVT) Ltd. Following is the main business process scenario of Sampath Food City (PVT) Ltd:

Details of all the customers should be stored in the database such as customer ID, name, address, date of birth, gender, email and contact no. Customers place orders. Details of the orders should be stored in the database such as order ID, order date, and order time. Orders contain products. Details of the products should be stored in the database such as product id, product name, unit price and total quantity. Products belong to categories. Details of the categories should be stored in the database such as category id, category name and description. One customer can place many orders and one order can be placed by only one customer. One order contains several products and one product can be contained in many orders. One product can belong to one category and one category has many products. 

Task

Understanding the logical structure of data through Entity-Relationship Diagrams (ERDs) is crucial. For this assignment, you are tasked with creating an ERD that illustrates the data structures and relationships inherent in the provided case study.

Upon completion of your ERD, next you need to translate the conceptual design into a relational schema, outlining the tables, attributes, primary keys, foreign keys, and any necessary constraints to reflect the logical structure of the database.

Normalization aims to reduce duplications. You are to produce a well-normalized database up the third Normal Form following your listing specifically identifying the primary and foreign keys and including user interfaces and output designs. The effectiveness of database design is usually assessed through testing. Assess your database design in relation to the user and system requirements.

2. After successful database design, the next stop is to build a fully functional relational database system using structured query language. Using your design as a guide, develop your database by all the tables using Structured Query Language with necessary attributes and declare primary and foreign keys where necessary. Ensure your implementation is justified to meet user requirements. The tables created must be populated with records of at least five (5) entries for each table to enhance querying your database. Include screenshots showcasing the user interfaces of the database system, along with evidence demonstrating the implementation of data validations within the database.

To justify the use of SQL, it is important to include screenshots of the SQL and the query outcome. To enhance your understanding, ensure to evaluate each of the queries in answering the transactions.

Provide evidence of the actions taken to implement database security and database maintenance.

Due to the data-specific nature of databases, it is important to assess the effectiveness of how these are ensured in your implementation of the fully functional database system by users and the system's requirements.

3. It is necessary to test the database and in the process of successfully carrying out testing, a test plan suffices. In your report, outline how the system has been tested against users and the system's requirements. This test plan is preferably to be in a table format and include a visual representation such as screenshots of results and annotations.

Using your test data explain the effectiveness of the testing including an explanation of the choice of data used.

4. Documentation helps in understanding the concept of database development. To reflect your understanding of technical and user documentation, you are required to produce fully technical and user documentation for your designed database. Your documentation should include a DFD diagram showing the movement of data through the system, and flowcharts describing how the system works.

Enhancing database development is paramount in completing the development cycle. You are required to assess any future improvements that may be required to ensure the continued effectiveness of the database system.

This Management has been solved by our PHD Experts at My Uni Paper.