Download Database Design and Development asm 1 and more Assignments Database Programming 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 30/10/2021 Date Received 1st submission
Re-submission Date Date Received 2nd submission
Student Name Lê Anh Minh Student ID GCD
Class GCD0904 Assessor name Đỗ Duy Thảo
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
Grading grid
P1 M1 D
❒ Summative Feedback: ❒ Resubmission Feedback:
Grade: Assessor Signature: Date:
Signature & Date:
- Database...............................................................................................................................................................................
- Chapter 1 - Statements of user and system requirements.......................................................................................................
- A. Overview about the Problem.............................................................................................................................................
- I. Problem...........................................................................................................................................................................
- II. User Story.......................................................................................................................................................................
- B. Requirements of the application.......................................................................................................................................
- I. Basic functions................................................................................................................................................................
- II. Advanced statistical actions.........................................................................................................................................
- Chapter 2 – Design the relational database system...............................................................................................................
- A. Analyse the requirements...............................................................................................................................................
- I. Program idea.................................................................................................................................................................
- II. ER Diagram...................................................................................................................................................................
- B. Database design with explanations................................................................................................................................
- I. Database Diagram:........................................................................................................................................................
- C. Review whether the database is normalized..................................................................................................................
- I. What does it mean when a database is normalized?....................................................................................................
- II. Normalization Rule.......................................................................................................................................................
- D. Data validation................................................................................................................................................................
- I. Data Type......................................................................................................................................................................
- II. Contraint.......................................................................................................................................................................
- Reference............................................................................................................................................................................
Chapter 1 - Statements of user and system requirements A. Overview about the Problem I. Problem Recently, FPT Greenwich University is planning to use the funds to build a dormitory for students studying at the university. All planning steps such as land lease, design, construction and budget calculation, .. have been assigned to professionals. However, everyone encounters a huge problem that is the management of students in the dormitory, so based on the design that I have collected, I will build a database to help solve this problem. II. User Story Information about the design of the classroom have collected at FPT Greenwich is as follows:
In general, we can clearly see the necessary information: Floor 1
Floor 2 Floor 3 After a while of collecting information, we have summarized everyone's opinions about database access as follows: As a manager, they would like to have the following benefits: Add and update student information.
II. Advanced statistical actions Statistics on asset damage Statistics on student payment Gender statistics of students in dormitory Statistics of available rooms Statistics of outdate bookings. Chapter 2 – Design the relational database system A. Analyse the requirements I. Program idea The idea of the program is that students can only book rooms for themselves, one student can book multiple bookings. The student management board will then review student information and their booking information to consider whether they are approved or not. The approved student will become a "Lodger". The "Lodger" will also be able to book multiple bookings to extend or return to the hostel. II. ER Diagram
1. explanation of tables: Students
This table contains the room information, include: ID is the booking ID and is set as the primary key to avoid duplicates and identity. Student ID is ID of Student table. Time join, Time out is start date and period of lodging time. Payment amount is the fee students have to pay if the booking is approved. Status: include 1 of 3 rejected, confirmed, paid. Room
The Room table contains information about the rooms in the dormitory. The ID is the room number, set the primary key and identity(1,1) to avoid duplication and automatically increment each row of records. Contains information such as room number, room type, room rate, room floor. Facility Table information of products have in the room ID is the code of the property Property Name is the property name Status is the status of the property Price is the price of the asset
B. Database design with explanations I. Database Diagram:
C. Review whether the database is normalized I. What does it mean when a database is normalized? Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. II. Normalization Rule Normalization rules are divided into the following normal forms: First Normal Form Second Normal Form Third Normal Form BCNF Fourth Normal Form First Normal Form (1NF) For a table to be in the First Normal Form, it should follow the following 4 rules: It should only have single(atomic) valued attributes/columns. Values stored in a column should be of the same domain All the columns in a table should have unique names. And the order in which data is stored, does not matter. In the next tutorial, we will discuss about the First Normal Form in details. Second Normal Form (2NF) For a table to be in the Second Normal Form,
Fourth Normal Form (4NF) A table is said to be in the Fourth Normal Form when, It is in the Boyce-Codd Normal Form. And, it doesn't have Multi-Valued Dependency. Here is the Fourth Normal Form tutorial. But we suggest you to understand other normal forms before you head over to the fourth normal form. D.Data validation I. Data Type some data types i will use in the tables are: char(n) Fixed width character string 8,000 characters Defined width varchar(n) Variable width character string 8,000 characters 2 bytes + number of chars varchar(m ax) Variable width character string 1,073,741,824 characters 2 bytes + number of chars nvarchar Variable width Unicode string 4,000 characters int Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes decimal(p, s) Fixed precision and scale numbers. 5-17 bytes
Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 date Store a date only. From January 1, 0001 to December 31, 9999 3 bytes II. Contraint Almost every column in the tables will default to ALLOW NULL except for mandatory information such as booking information,... for the convenience of information creation, information that is not available can be updated later. In the database, I will use some more constraints besides Primary Key, Foreign Key and NOT NULL: Gender will be bound to 3 types of gender including: Male, Female, Other. ALTER TABLE Students ADD CHECK(Gender='Male' or Gender='Female' or Gender='Other'); Status of table Booking have to be NULL or Denied, Confirmed, Paid. ALTER TABLE Booking ADD CHECK(Status='Denied' or Status='Confirmed' or Status='Paid');
