Putting data in mySQL WorkBench
BibekG
ProjectGrp15.docx· Final Project- Kindergarten Management Database
· Group 15 – Pooja Timalsina & Prithivi Acharya
Project Goals
The goal of this project is to create a database for kindergarten. In order to reach the goal, three aspect Cost, Timing and Function are considered in account. This database will serve a means of storing data for kindergarten for records in efficient manner and user- friendly environment.
Database Description
The database is used to track multiple set of data to operate Kindergarten business. Every one of these tables would have various fields that are important to the data stored in the table. The database use MYSQL as database management system. The database stores and track kid information, health records, employee information, inventory stocks and finance details. Using 6 table, all the records are stored. There are few limitation and assumption taken in consideration due to its business model and client special needs. Database use integer, variable and decimal data to satisfy the character of data in table. Also, in order to input any data maximum no of column should be filled in order to obtain maximum data as possible.
Data model & Design
DATA DICTIONARY
TABLES:
· EMPLOYEE
|
COLUMN NAME |
DATA TYPE |
COLUMN RESTRICTION |
COLUMN DESCRIPTION |
|
ENum |
INTEGER |
NOT NULL
|
|
|
FirstName |
CHARACTER |
|
|
|
LastName |
CHARACTER |
|
|
|
Department |
CHARACTER |
|
|
|
Contact |
CHARACTER |
|
|
· INVENTORY
|
COLUMN NAME |
DATA TYPE |
COLUMN RESTRICTION |
COLUMN DESCRIPTION |
|
Department |
CHARACTER |
NOT NULL & NULL |
|
|
Pen |
INTEGER |
|
|
|
Pencil |
INTEGER |
|
|
|
Marker |
INTEGER |
|
|
|
Copier |
INTEGER |
|
|
· TRANSACTION
|
COLUMN NAME |
DATA TYPE |
COLUMN RESTRICTION |
COLUMN DESCRIPTION |
|
TransID |
INTEGER |
NOT NULL |
|
|
Category |
CHARACTER |
|
|
· KID
|
COLUMN NAME |
DATA TYPE |
COLUMN RESTRICTION |
COLUMN DESCRIPTION |
|
KidId |
INTEGER |
NOT NULL & NULL |
|
|
FirstName |
CHARACTER |
|
|
|
LastName |
CHARACTER |
|
|
|
DOB |
DATE |
|
|
|
TeacherID |
INTEGER |
|
|
|
Class |
CHARACTER |
|
|
|
City |
CHARACTER |
|
|
|
ParentsPhone |
CHARACTER |
|
|
· HEALTHRECORDS
|
COLUMN NAME |
DATA TYPE |
COLUMN RESTRICTION |
COLUMN DESCRIPTION |
|
ID |
INTEGER |
NOT NULL |
|
|
Illness |
Boolean |
|
|
|
SpecialNeed |
Boolean |
|
|
|
Allergies |
Boolean |
|
|
|
Immunization |
Boolean |
|
|
|
MedicalRecord |
CHARACTER |
|
|
|
HealthRating |
DECIMAL |
|
|
|
Doctor |
INTEGER |
|
|
· FINANCE
|
COLUMN NAME |
DATA TYPE |
COLUMN RESTRICTION |
COLUMN DESCRIPTION |
|
TransID |
INTEGER |
NOT NULL |
|
|
DATE |
DATE |
|
|
|
TransactionDetail |
CHARACTER |
|
|
|
ID |
INTEGER |
|
|
|
Amount |
DECIMAL |
|
|
Assumption and limitation of Design
· FINANCE TABLE hold information for Kid only
· HEALTHRECORDS TABLE hold Kid health records only.
· Address is shortened to city name.
· SSN and official documents for EMPLOYEE are not mentioned for security reasons.
· Kid parent contact information is telephone entry.
· Amount for same TransID are same.
· Inventory is based on current stock.
Prithivi Acharya
|
2 trivial query. Simple select with ordering |
|
The query output all the information of kid that live in city Denton and their class are ordered in alphabetic order.
The query returns the information of Employee whose name start with D and are sorted according to their Last name.
|
|
2 medium difficulty queries. Queries that use composite condition for selection, computations, aggregate function and grouping. |
|
The query output the no of kid that each teacher have. It has the condition that shows only those TeacherID and counting of kind where TeacherID is more than or equal to 180 and less than or equal to 230.
The query output TransID, TransactionDetail and total sum of each transaction where transaction detail start from Student and TransID is greater than 2000.
|
|
2 query that uses subquery. |
|
The query use subquery to list id and name for those students whose health is examined by doctor with id 300. In HEALTHRECORDS table there are only KidID mentioned, We pull out the Kid First name and last name from KID table using subquery.
|
|
3 queries that uses join (1 inner join, 1 left join, 1 right join). |
|
The query uses inner join to output the result for only those students that met the above condition. Here We output only those student who are related to Trans ID 1100 in FINANCE TABLE.
Left join include all the data from left table and matching value from right table. Here in above query , we display all the student with matched value and also those student who don’t have any value at TRANSACTION table.
Right join includes all the data from right table and matching value from left table. Here in above query we output all kid value and also the health record value which are not for those kids.
|
|
2 view (1 query must use join and the other must use subquery) |
|
The query uses two view one is sub query and other is join to output first name and last name for employee who work in department Teacher.
|
|
1 query that uses union |
|
|
Pooja Timalsina
|
2 trivial query. Simple select with ordering |
|
The query output Employee no, name and department for all the employee whose Enum is greater than 150.
The query output all the data for INVENTORY Table who have more than 90 pen on stock.
|
|
2 medium difficulty queries. Queries that use composite condition for selection, computations, aggregate function and grouping. |
|
The query output counting of student which have allergies and are checked by doctor 300 and 310 So that they can follow up for coming event.
The query output total amount of money for Trans ID 1100 and 2200. Student fee and insurance amount are summed up for the output.
|
|
2 query that uses subquery. |
|
The above query use subquery to output id and name of those employee who teaches on class A and B .
The above query use subquery to list id for kid, transaction detail and amount for Kid who have student fee paid.
|
|
3 queries that uses join (1 inner join, 1 left join, 1 right join). |
|
The query use inner join to list those doctor who check on KID with no allergies.
Left join in above query output those EMPLOYEE from that table who match the data and also output whose are remaining on left table.
The query output FirstName and last Name of employee and first name for Kid and right join also include those employee that are not teacher but are in different department.
|
|
2 view (1 query must use join and the other must use subquery) |
|
The above query use subquery to output id of KID for all those transaction related to student fee and insurance.
The above query use join to output id of KID for all those transaction related to student fee and insurance.
|
|
1 query that uses union |
|
Union output same data type and output them as one data. Here we output first name, last name and contact for Kid and Employee using union.
|
