Download Database Design and Development for Smart Movers Transport and more High school final essays Computer science in PDF only on Docsity!
Database Management System for
Smart Movers Transport Company
Database Design & Development
StuDocu is not sponsored or endorsed by any college or
university
This document provides a comprehensive elaboration of the given text, which is a Higher National Diploma (ESOFT Metro Campus) assignment on Database Design and Development. The assignment is for the Unit 4: Database Design & Development, and the student's name is F. Thameena Banu.
Task 1 - (LO1) Use an appropriate design tool to design a
relational database system for a substantial problem
Task 1.1 (P1) Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with clear statements of user and system requirements.
a. The user and system requirements for the database design are not explicitly provided in the text. However, the assignment is for a "Data base system for the SmartMovers Transport Company", so the requirements can be inferred to be related to the operations and management of the transport company.
b. The text instructs the student to identify the entities and attributes of the given scenario and design a relational database system using a conceptual design (ER Model). The ER Model should include identifiers (primary keys) of entities and cardinalities, as well as the participations of relationships.
c. The text then requires the student to convert the ER Model into a logical database design using the relational database model, including primary keys, foreign keys, and referential integrities. The logical design should contain at least five interrelated tables.
The text allows the student to make their own assumptions and include related attributes within the scope of the case study.
Task 1.2 (M1) Produce a comprehensive design for a fully functional system, which includes interface and output designs, data validations, and data normalization.
a. The student is required to explain data normalization with examples and check whether the provided logical design in Task 1.1 is normalized. If not, the student should normalize the database by removing any anomalies.
b. The student is asked to design a set of simple interfaces to input and output data for the given scenario, using a wireframe or any other interface- designing tool.
c. The student should explain the validation mechanisms to validate data in the tables, providing examples.
Task 1.3 (D1) Assess the effectiveness of the design in relation to user and system requirements.
a. The student should check whether the given design (ER Diagram and Logical Design) represents the identified user and system requirements to assess the effectiveness of the design.
Task 2 - LO2 Develop a fully functional relational database
system, based on an existing system design
Task 2.1 (P2) Develop the database system with evidence of user interface, output, and data validations, and querying across multiple tables.
a. The student should develop a relational database system according to the ER diagram created in Task 1.1, using SQL DDL statements.
b. The student should provide evidence of the use of a suitable IDE to create a simple interface to insert, update, and delete data in the database.
c. The student should implement the validation methods explained in Task 1.2-part c.
d. The student should display the details of payments with the job details and the customer details using join queries.
Task 2.2 (P3) Implement a query language into the relational database system.
a. The student should explain the usage of DML (Data Manipulation Language) with the following queries, providing at least one single example per case from the developed database: - Select - Update - Delete
The queries should be meaningful and relevant to the given scenario.
Task 2.3 (M2) Implement a fully functional database system, which includes system security and database maintenance.
a. The student should explain how security mechanisms have been used and the importance of these mechanisms for the security of the database. The student should also implement proper security mechanisms (e.g., user groups, access permissions) in the developed database.
Task 4.3 (D3) Assess any future improvements that may be required to ensure the continued effectiveness of the database system.
a. The student should assess any future improvements that may be required to ensure the continued effectiveness of the database system.
Relational Database Design for a Logistics
Company
User and System Requirements
User Requirements: - Record all the details about the customers, products, payments, jobs, loads, depots, and transport units. - Ensure the data is secured.
System Requirements: - The company needs computers to run the database application. - The employees who have knowledge to maintain the database. - The software requirements include Windows 10 client version and SQL Server 2017. - The hardware requirements include an initial disk space of 160 MB and a minimum of 6 GB of RAM for the SQL Server.
Entity-Relationship (ER) Diagram
The ER diagram for the given scenario is as follows:
The entities and their corresponding attributes are:
| Entity | Attributes | |---------------|--------------------------------------------------------------| | Customer | Cus-no, C-name, Address, T-no | | Product | P-id, Type | | Job | Job-no, S-loc, Des | | Load | Load-no, L-type | | Deport | Deport-no, D-loc | | Payment | Invo-no, Pay-d, Amount | | Transport Unit| Cont-no, Dri-id, Amo, Lor-no |
Relational Database Design
The relational database design based on the ER diagram is as follows:
Customer(Cus-no, C-name, Address, T-no) Product(P-id, Type) Job(Job-no, S-loc, Des, Cus-no, P-id) Load(Load-no, L-type, Job- no) Deport(Deport-no, D-loc) Payment(Invo-no, Pay-d, Amount, Cus- no, Job-no) Transport_Unit(Cont-no, Dri-id, Amo, Lor-no, Job-no)
The primary keys are underlined, and the foreign keys are indicated in italics.
ER Diagram
Data Normalization
The provided logical design is not fully normalized. Let's apply the normalization process to remove any anomalies.
First Normal Form (1NF): The given design is already in the first normal form, as all the attributes have atomic values, and there are no repeating groups.
Second Normal Form (2NF): The design is in the second normal form, as all the non-key attributes are fully dependent on the primary key.
Third Normal Form (3NF): The design is not in the third normal form, as there are transitive dependencies. For example, in the Payment table, the Cus-no and Job-no attributes are transitively dependent on the Invo-no primary key.
To achieve the third normal form, we need to separate the Payment table into two tables: Payment and Customer_Job.
Payment(Invo-no, Pay-d, Amount) Customer_Job(Cus-no, Job-no, Invo-no)
Boyce-Codd Normal Form (BCNF): The design is now in the Boyce-Codd normal form, as all the functional dependencies have a superkey on the left- hand side.
Interface Design
Here is a simple wireframe design for the input and output interfaces:
Input Interface: - Customer Information: Cus-no, C-name, Address, T-no - Product Information: P-id, Type - Job Information: Job-no, S-loc, Des, Cus-no, P-id - Load Information: Load-no, L-type, Job-no - Deport Information: Deport-no, D-loc - Payment Information: Invo-no, Pay-d, Amount, Cus-no, Job-no - Transport Unit Information: Cont-no, Dri-id, Amo, Lor-no, Job-no
Output Interface: - Customer List: Cus-no, C-name, Address, T-no - Product List: P-id, Type - Job List: Job-no, S-loc, Des, Cus-no, P-id - Load List: Load- no, L-type, Job-no - Deport List: Deport-no, D-loc - Payment List: Invo-no, Pay-d, Amount, Cus-no, Job-no - Transport Unit List: Cont-no, Dri-id, Amo, Lor-no, Job-no
The wireframe design should provide a clear and intuitive interface for the users to interact with the database system.
Some common validation mechanisms include:
Check Digit : A redundancy check used for error detection, such as the ISBN-10 numbering system for books, which uses the "Modulo-11" division to generate a check digit.
Type Check : Ensures that the correct data type is inputted, such as using a number data type for an age field to prevent the input of verbal data.
Length Check : Ensures that the data entered is not too short or too long, such as ensuring that a password field is exactly 8 characters long.
Format Check : Checks if the entered data is in the right and appropriate format, such as ensuring that a National Insurance number is in the correct format (XX 99 99 99 XX).
Database Constraints
Constraints are used to ensure the data entered into a relational database is within a specified limit. The purpose of constraints is to maintain data integrity during updates, insertions, and deletions. Some common types of constraints include:
Not Null : Ensures that a column does not hold any null or empty value.
Unique : Ensures that the values in a column are unique, and that the column cannot have duplicate values.
Check : Ensures that the values in a column are within a specified range or meet a certain condition.
Primary Key : Defines a unique identifier for a table, and ensures that the values in the primary key column are unique and not null.
Foreign Key : Establishes a relationship between two tables by referencing the primary key of another table.
Effectiveness of the Database Design
The database design described in the scenario represents the identified user and system requirements, and is effective in addressing the needs of the Smart Movers transport company. The design includes all the necessary tables and relationships to store and manage the customer, product, transport, payment, deport, and load information, as required by the scenario.
The design ensures that all the user requirements are recorded in the system, and the data is stored in a centralized and secure manner. The use of primary and foreign keys to establish relationships between the tables ensures data integrity and facilitates efficient querying and retrieval of information.
Overall, the database design is comprehensive and effective in meeting the requirements of the Smart Movers transport company.
Developing the Relational Database System
The provided SQL DDL statements demonstrate the creation of the database and the tables according to the ER diagram. The screenshots show the execution of SQL commands to create the database, tables, and insert sample data into the tables.
User Interface and Data Validation
The use of Microsoft Visual Studio as the integrated development environment (IDE) provides a suitable platform for creating a simple user interface to interact with the database. Visual Studio supports a wide range of programming languages and allows for the implementation of various data validation mechanisms, as discussed in the previous section.
The implementation of the validation methods, such as check digit, type check, length check, and format check, ensures the integrity and validity of the data entered into the system, preventing errors and ensuring the consistency of the stored information.
Primary Key
A primary key is the unique identification in a table. When a record is created, the primary key assigned to it cannot be duplicated. A table can have only one primary key. The primary key is a specific choice of a minimal set of attributes that uniquely specify a row in a table. The purpose of a primary key in a table is to ensure row-level accessibility. Examples of primary keys include:
Customer ID Job number Invoice number Load number
Foreign Key
A foreign key is taken from the primary key of another relation. Foreign keys act as cross-references between tables. Foreign keys help to identify a row of another table. Foreign keys do not have to be linked specifically to the primary key in another table.
Unique Constraint
The unique constraint ensures that the values in a column are unique. If a column has a unique constraint, the column cannot have duplicate values in the table. The primary key automatically has a unique constraint.
sql DELETE FROM [table name] WHERE [condition]
Database Security Mechanisms
Database security is a top priority for today's organizations. It involves the use of a broad range of information security controls to protect databases against compromises of their confidentiality, integrity, and availability. Some of the key security mechanisms include:
Access Control
Access control is the selective restriction of access to a resource. It involves authorizing users to perform specific actions on the database.
Auditing
Database auditing involves observing a database to be aware of the actions of database users. This is often done for security purposes, to ensure that unauthorized users do not access information.
Authentication
Authentication is the process of confirming that a user attempting to log in to a database is authorized to do so. It typically involves one or more of the following factors: something you know (e.g., a password), something you have (e.g., a smart card), or something you are (e.g., a biometric method).
Encryption
Encryption is the process of transforming data stored in a database into 'cipher text' that is incomprehensible without first being decrypted. Encryption helps to protect the confidentiality of data.
Integrity Controls
Integrity controls ensure the overall completeness, accuracy, and consistency of data in the database. This is usually implemented through the use of standard procedures and rules, such as entity integrity, referential integrity, and domain integrity.
Backups
Backups are copies of data from the database that can be used to reconstruct that data in the event of a failure or data loss. Backups can be physical (e.g., full database backups) or logical (e.g., data exports).
SQL Statements
GROUP BY
The GROUP BY statement is used to group the data by one or more columns. It is often used in conjunction with aggregate functions, such as SUM, AVG, or COUNT, to perform calculations on the grouped data.
Example: sql SELECT payment_amount, COUNT(*) AS total_payments FROM payment GROUP BY payment_amount
ORDER BY
The ORDER BY statement is used to sort the data in ascending or descending order based on one or more columns.
Example: sql SELECT * FROM customer ORDER BY customer_name DESC
HAVING
The HAVING clause is used to apply a condition to the aggregate results to limit the groups of returned rows.
Example: sql SELECT payment_amount, SUM(payment_amount) AS total_payments FROM payment GROUP BY payment_amount HAVING SUM(payment_amount) > 1000
BETWEEN
The BETWEEN clause is used to get a set of values within a specified range.
Example: sql SELECT * FROM job WHERE job_date BETWEEN '2022-01-01' AND '2022-12-31'
WHERE
The WHERE clause is used to apply a condition to select, update, insert, or delete data.
Example: sql SELECT * FROM customer WHERE customer_name = 'John Doe'
Test Plan
The test plan for the Smart Movers database system should cover the following aspects:
Verifying the behavior when additional data is added to the tables. Checking the system's response to data types that are entered incorrectly.
User's Guide
This is the Smart Movers automated system for operational activities. The users' guide will help you to work with the system.
Sign In
This is the welcome page which will be shown soon after you run the program. If you are an existing user: 1. Enter your User ID 2. Enter your password
If you are a new user, click "Sign up" to create a new account.
New Account Registration
This is the new account page for new customers. Enter your details correctly, create a new password and remember the password for your sign in purpose. After entering all the relevant details, click on the "Submit" button to register your account and make your payment.
Payment
In this page, type your card details to register with SmartMovers and importantly select your type of membership from "Category 1/2/3". After that, click on "Pay" to complete the registration.
Profile
This is the Profile of the SmartMovers. After you log in to the system, you will get here. In this page, you can make a new delivery by clicking "New Delivery" button, see your current deliveries by clicking "Current Deliveries" and see your delivery history by clicking on "Delivery History".
New Delivery
In this window, you have to provide your product and load details to make an order. After you complete filling the details, then click on the "Confirm and pay" button and it will redirect you to the merchant payment site. After you completed the payment, your order details will be shown. Finally, you can go to the homepage again by clicking on the "Continue to homepage" button.
Technical Documentation
To run the program smoothly and for better performance and maintenance, the following requirements are needed.
Visual Studio 2017
Hardware
1.8 GHz or faster processor. Dual-core or better recommended 2 GB of RAM; 4 GB of RAM recommended (2.5 GB minimum if running on a virtual machine) Hard disk space: up to 130 GB of available space, depending on features installed; typical installations require 20-50 GB of free space. Hard disk speed: to improve performance, install Windows and Visual Studio on a solid state drive (SSD). Video card that supports a minimum display resolution of 720p ( by 720); Visual Studio will work best at a resolution of WXGA (1366 by
- or higher.
Supported Operating Systems
Windows 10 version 1507 or higher: Home, Professional, Education, and Enterprise (LTSB and S are not supported) Windows Server 2016: Standard and Data center Windows 8.1 (with Update 2919355): Core, Professional, and Enterprise Windows Server 2012 R2 (with Update 2919355): Essentials, Standard, Data center Windows 7 SP1 (with latest Windows Updates): Home Premium, Professional, Enterprise, Ultimate
Microsoft SQL Server 2012
Component Requirement
Memory: Minimum: Express Editions: 512 MB All other editions: 1 GB Recommended: Express Editions: 1 GB All other editions: At least 4 GB and should be increased as database size increases to ensure optimal performance. Processor: Minimum: 1.0 GHz Recommended: 2.0 GHz or faster Processor Type: x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support x86 Processor: Pentium III-compatible processor or faster
