HOMEWORK GROUP

1. GYM MANAGEMENT SYSTEM

A yoga/gym center needs to develop an application for management purposes. Users register an
account to become customers. A customer account includes an account name (which must be
unique for each customer), full name, and password. To use a service package at the center, a
customer must log in and purchase a training pass. The application will list the available service
packages. Each service package includes the following information: package ID, package name,
price, number of training sessions, and duration of each session. The customer selects a service
package to purchase and proceeds with the payment. If the payment is successful, the application
will issue a training pass to the customer. The information on a training pass includes: a unique
pass number, purchase date, the selected service package, and the customer. Each time the
customer visits the center, a staff member will record a training session on the pass. The
information for each session includes: the session number and the date and time of check-in.

2. RESTAURANT MANAGEMENT SYSTEM

A restaurant wants to develop an application to manage order-taking and payment processing for
cashier staff. The application needs to store a list of employees. Each employee has the following
information: employee ID, full name, username, and password. The employee ID is unique for
each employee. The application also contains a list of dishes served by the restaurant. Each dish
includes: dish ID, dish name, and price (the dish ID is unique). When a customer arrives and
places an order, the employee will use the application to record the order slip for the customer.
Each order slip includes: a unique order number, order date, the table where the customer is
seated, the serving employee, and the selected dishes along with their quantities.

3. APPLICATION FOR POS

Cashiers are those using this application to record customer purchase. Every cashier when
starting their shift must login to use this application. The login information includes Account and
Password. When a customer brings their shopping cart to the counter for payment, the cashier
will create a receipt on the application, the application will automatically generate an ID for this
receipt, also includes the date the receipt is created, the name of the cashier who is responsible
for creating the receipt. The cashier will enter the ID of each product in the cart including its
quantity. The application will reveal the name of the product, the price per one product alongside
computing the price of that product, then the price of the whole cart. After reaching the final
product, the cashier chooses the payment option, enter the customer’s total payment, then the app
reveals the reimbursement for the customer. The cashier chooses to print the receipt as a result of
finishing the purchase.

4. MULTIPLE-CHOICE QUESTIONS SYSTEM

This is an application designed for students to test their knowledge across various school subjects
based on their grade level. The question bank and the list of students are pre-configured in the
application.

Each subject at each grade level will have a number of multiple-choice tests. Each test specifies
the number of questions and the time allowed for completion.

To take a test, students log into the application using a pre-provided account. Based on their
grade level and the tests they’ve already taken, the application will display a list of tests available
for them to take.

The student selects a test from the list and begins. The questions for the test are randomly
selected by the application from the question bank. The application will display each question
one at a time. For each question, the student chooses one of four answer options: a, b, c, or d.
They may also skip a question to move to the next one. Additionally, the student can place a flag
icon to mark any question they’re currently working on.

The student proceeds to the next question using a "Next" button. Once they reach the final
question, they can choose the "Review" option to view a summary table of all questions along
with selected answers and any flags. From this summary view, the student can choose any
question to revisit and modify their answer.

When the student is ready to submit their test, they select the "Submit" option. After submission,
the application calculates and records the score for that test and displays the correct answers for
each question.

Additionally, once logged in, students can also view the results of all previously completed tests.

5. QUESTION BANK MANAGEMENT SYSTEM

A high school wants to create a question bank for exam purposes. This is an application designed
for teachers to enter exam questions into the bank. The application already has a list of teachers
and their roles, as well as the subjects for each grade level.

To use the application, a teacher must log in with their account and password. The application
will then display a list of grade levels along with the subjects for each level. The teacher selects a
subject from the list, and the application will display a list of multiple-choice exams for that
subject. Each exam includes information such as: exam number, exam name, number of
questions, exam duration, and a link to the question bank. When the teacher selects the question
bank section, the application will display a list of multiple-choice questions that have already
been added to the bank for that exam. Each question shows information like the question number
and a brief summary of the content. If the teacher chooses to view the question in detail, the
application will show the full content, the answer choices, the person who reviewed the question,
and the person who created it.
If the teacher is the coordinator for the subject at a particular grade level (e.g., coordinator for
Grade 9 Math), they will have the following responsibilities:

 Add/edit/delete exam information for a subject. The exam information includes: exam
number, exam name, exam duration, and number of questions.
 Review multiple-choice questions: the application will display a list of questions
submitted by the subject teachers. The coordinator can choose to approve or reject each
question for inclusion in the exam bank.

If the teacher is an instructor for a subject at a particular grade level, they will have the following
responsibilities:

 Add/edit/delete multiple-choice questions for the exams of the subject they teach. For
each question, the teacher will input the following information: question content, four
answer choices (a, b, c, d), and the correct answer.

6. PROJECT MANAGEMENT SYSTEM

This application is designed for project managers in a company to support their work.

Project managers log into the system using their account and password. When they are assigned
to manage a new project, they will create that project in the application. Basic information for a
project includes the project name, status, and expected completion time. The manager will also
assign employees to the project by selecting them from the company’s employee list. The
manager can edit or delete a project as needed.

Next, the project manager will create a plan for the project within the application by entering
tasks, scheduled timelines, costs, and assigning employees to each task.

During the execution of the project, the manager will update the application with assessments of
each employee's work performance and estimate the percentage of task completion according to
the milestones.

When the project ends, the manager will input actual cost and actual completion time for each
task. The application will generate a summary report and statistics for the entire project.

Once the manager marks a project as completed, the project’s information can no longer be
edited or deleted.

Managers can also use the application to review all the projects they have managed.

7. EMPLOYEE ATTENDANCE AND SALARIES MANAGEMENT

This application is used to record employee attendance and calculate salaries. The users of the
application include:
  Department Timekeepers: Timekeepers log into the system. The application displays a
list of employees in their department and the attendance days for the month. Each day,
the timekeeper records attendance for each employee, noting whether they were absent
and the reason: with permission, without permission, on business trip, etc. At the end of
the month, the timekeeper submits the attendance report to the HR department. If HR
does not approve the attendance report, the timekeeper must revise the data and resubmit
it for a second review.
 Human Resources (HR): HR personnel log into the system to review submitted
attendance reports. The application shows the attendance sheets sent from each
department. If an attendance report is acceptable, HR approves it. If not, HR selects
"Reject" and enters feedback requiring the department’s timekeeper to revise the report.
Then HR will review the resubmitted report.
 Accounting: Accountants log into the system to check if all attendance reports have been
approved. When the salary payment date arrives, and all approvals are complete,
accounting initiates a salary transfer order to the bank.
 Cashier: The cashier logs into the system. When an employee arrives to receive their
salary in cash, the cashier enters the employee's ID number. The application displays the
amount the employee is to receive. The cashier hands over the money and records that the
salary has been paid.

8. CUSTOMER SERVICE SYSTEM

An application for managers and customer service staff to track orders. Users must log in to the
application using an existing account and password. The application already contains a list of
customer service employees and a list of orders.

Managers use the application to assign employees to follow up on specific orders. When an
employee logs into the system, the application displays a list of orders they are responsible for.
Each order will have one of the following statuses: not yet shipped, in transit, or delivered.

When a customer calls to inquire about their order, the employee can search for and respond to
the customer’s questions regarding the order and the items they purchased.

If a customer requests to return a purchased item, and the return is approved, the employee will
record the returned item on the order and assign a delivery staff member to pick up the product.
When the delivery staff returns the item to the warehouse and confirms it, the customer service
employee will record the return as complete.

9. ONLINE SHOPPING WEBSITE

A store wants to build an online shopping website. The website is described as follows:

Products will be uploaded to the website for customers to view and choose from. Each product
listing will include the following information: product ID, product name, selling price, and
description. Products are categorized by departments to make it easier for customers to search.
Each product has a unique product ID. Each department has a unique name. A product belongs to
only one department, while a department can contain many products.

Customers must register their personal information before making purchases on the website. The
registration page will require the following details: last name, first name, gender, phone number,
email, address, login account, and password. Each customer has a unique account.

To make a purchase on the website, the customer logs in using their account and password, then
proceeds to shop as follows:

 Create a new shopping cart. Each shopping cart contains: cart ID, creation date, and the
customer who created the cart.
 Customers select products and the quantity they wish to buy to add to their cart. The
shopping cart will be saved.

The store has a team of delivery staff. The system stores information about each delivery
employee, including: employee ID, full name, national ID number, and phone number. For each
cart that a customer has purchased, the store assigns one delivery employee to handle the
delivery. A delivery employee may deliver multiple shopping carts.

10. SALES MANAGEMENT

A store needs a database for building a sales management application. The store needs to track
supplier information including: supplier ID, supplier name, address, and phone number. The
supplier ID is unique for each supplier. Each supplier supplies multiple items. Each item has the
following information: a unique item ID, item name, and price. An item is supplied by only one
supplier.

The store has multiple stalls, each with a unique number and a name. Each stall sells a number of
items, and each item is sold at only one stall.

The store employees need to have the following information recorded: employee ID, name, and
address. The employee ID is unique for each employee. When a customer makes a purchase, the
employee will create an invoice for the customer. Each invoice contains the invoice ID, creation
date, the employee who created it, the customer, and the purchased items with their price and
quantity.

Each customer is required to provide the following information: full name, address, and phone
number. Each customer is assigned a unique ID.

11. LIBRARY WEBSITE

The library introduces books to readers through its website. Each book includes the following
information: book ID, book title, author name, publisher, year of publication, number of pages,
and a summary of the content. To make it easier to search, books are categorized by topic. Each
topic includes a topic ID and topic name.
To become a library reader, individuals must fill out a registration form. The form requires full
information including: full name, gender, date of birth, address, phone number, and email. The
library will assign a unique ID to each reader.

In each book acquisition period, the library will acquire a certain number of copies for each book
title. Each physical copy received will be assigned a unique copy ID (book copy ID), and the
library will also record the acquisition date and purchase price of each copy.

When a reader wants to borrow books, they will go to the shelves, select the books they want to
borrow, and bring them to the borrowing desk. A library staff member will record the borrowing
transaction, which includes: borrowing ID (an incrementing number), reader ID, each book copy
ID, and the borrowing date.

When returning books, readers bring the book copies to the return desk. The staff will record that
the reader has returned each book and the return date.

12. CLINIC MANAGEMENT

A clinic needs to build a database to store information for managing patients. Each time a patient
visits the clinic for the first time, they will provide the clinic with the following information: full
name, date of birth, phone number, address, and gender. The clinic will then assign a unique
patient ID to each patient.

Each time the patient comes for a check-up, the clinic will record a medical examination form.
This form includes the following information: form ID, patient ID, examination date, weight,
height, blood pressure, heart rate, and the diagnosis. During each visit, the patient may (or may
not) be prescribed medication. A prescription for a patient consists of one or more medications,
with each medication including details about the number of days it should be taken, the number
of doses per day, and the dosage for each dose. The medications prescribed are from the clinic's
medication list.

Information for each medication in the medication list includes: medication ID, medication
name, active ingredient, route of administration, dosage form, and medication group.

For each medication import period, the clinic will record the details of the medication import
form. The import form includes: import form ID, import date, and the list of medications
imported (medication ID, quantity, unit price, batch number, and production date).

13. SUPERMARKET MANAGEMENT SYSTEM

A supermarket needs a database to build an information system. The supermarket needs to know
the information of suppliers such as: supplier ID, supplier name, address, and phone number. The
supplier ID is unique for each supplier. Each supplier provides multiple products. Each product
has information including a unique product ID, product name, and price. A product is only
supplied by one supplier.
The supermarket has many departments, each of which is uniquely numbered and has a name.
Each department has a department manager and many employees. Each department sells a
number of products, and each product is sold in only one department. An employee can work in
multiple departments.

14. COMPUTER CENTER

A computer center needs to build a database for the center's teaching activities. Each month, the
center offers many courses: course ID, course name (Windows, Word, etc.), start date, end date,
class time, sessions, and tuition fee. Each course is taught by one teacher, but a teacher can teach
multiple different courses (if the times do not overlap). Teacher information includes: teacher ID,
full name, and address. Students register to attend courses and can enroll in different courses (if
the times do not overlap). Student information includes: student ID, full name, and address. At
the end of each course, students will take an exam and receive a grade for the course they
attended. Each course only records one grade for each student. Teachers will participate in
compiling textbooks (for both the subjects they teach and those they do not teach). Textbook
information includes: textbook ID, textbook name, number of pages, and price. After finishing a
course, students will receive a certificate (certificate ID, certificate name, and classification) for
each course they attended and passed with a grade of 5 or higher.

15. CAR REPAIR SHOP

A car repair shop wants to build a system to manage car repair and maintenance activities. The
workshop is organized as follows:

The garage has many mechanics and multiple repair tasks. The information to be stored about the
mechanics includes: Each mechanic has an ID, full name, and belongs to a team. The team leader
of each team is one of the mechanics in that team. Each repair task includes: task ID, detailed
task name, and mechanic's labor cost. Each time a customer brings a car for repair, they sign a
repair contract. A customer can sign multiple repair contracts for different cars.

The information to be stored about customers and contracts includes:

 Each contract has a contract ID, contract creation date, cars to be repaired, contract value,
and acceptance date. (The car license plate is the registration number of the car being
repaired, issued by the Road Traffic Police Department; if the car changes owner, it is
considered a different car).
 The repair tasks under the contract are also stored. Each task corresponds to a repair
category, and the price is calculated as the cost of parts + labor charge.

The information about the cars being repaired includes: license plate number, car name, and car
type. After the contract is signed, the garage will assign mechanics to handle the repair tasks.
Each task under the contract is assigned to one mechanic. A mechanic can be assigned to
multiple tasks on the same contract.
Each customer has a customer ID, full name, address, and phone number. If a customer makes
payments for a repair contract in multiple installments, either before or after the acceptance date
(on the same day or different days), a receipt and a warranty certificate must be issued.

The information to be stored about the receipt and warranty certificate includes:

 Each receipt has a receipt ID, issue date, amount paid, contract ID, and payment
installment.
 Each warranty certificate has a warranty certificate ID, warranty start date, warranty
period, contract ID, car being warranted, and the parts under warranty.

The garage also maintains a list of parts, where each part has information such as part ID, part
name, manufacturer, production date, and price.

16. SHOP MANAGEMENT SYSTEM

A shop wants to develop an online shopping website. The website is described as follows:

Products are listed on the website for customers to browse and choose from. The product
information on the website includes: product ID, product name, price, and description. Products
are categorized by product category to make it easier for customers to search. Each product has a
unique product ID. Each product category has a unique name. A product belongs to only one
category, while a category can contain many products.

Customers need to register their personal information before making purchases on the website.
The customer registration form requires the following information: last name, first name, gender,
phone number, email, address, login account, and password. Each customer has a unique
account.

To make a purchase on the website, the customer logs in using their account and password, then
proceeds with the shopping process as follows:

 Create a new shopping cart. A shopping cart includes: cart ID, creation date, and the
customer who created the cart.
 The customer selects products and specifies the quantity to add to the cart. The
customer's cart is saved.

The shop has a team of delivery staff. The system stores information for each delivery staff
member, including: staff ID, full name, ID card number, and phone number. For each cart that
has been purchased, the shop assigns a delivery staff member to deliver it. One staff member can
deliver multiple carts.
 17. PRESS DISTRIBUTION CENTER

A Press Distribution Center consists of multiple branches. Information about a branch includes:
Branch Code, Branch Name, Address, and Phone Number (e.g., BR1, Branch Camden Town,
123 Camden High Street, tel 8665544).

The center supplies various types of publications (including daily newspapers, weekly
newspapers, magazines, etc.) to customers through its branches. No publication is issued by
more than one publisher. Each publication includes the following information: Publication Code,
Publication Name, Issue Frequency, and Selling Price (e.g., TTEN, The Daily News, 6
issues/week, £4.00). If the publication is a newspaper, there is additional information about the
paper type; if it is a magazine, additional information includes its dimensions.

Publisher information includes: Publisher Code, Publisher Name, Address, and Phone Number
(e.g., LDN, London Press Office, 432 Oxford Street, tel 8395942). Multiple different
publications may be issued by the same publisher and vice versa.

A person may subscribe to multiple types of publications through different branches to become a
customer of the center. If the customer is registering with the center for the first time, through
any branch, their personal information must be recorded: Customer ID, Full Name, Organization
(if any), Contact Address, Phone Number, and Email (e.g., CU1003, John Smith, 25 Regent
Street, London).

For subsequent registrations, only the Customer ID is needed to identify the customer; there is no
need to re-enter their information. For each publication subscription, the customer receives a
subscription form. This form includes the Subscription ID, Date of Issue, Subscription Duration
(3, 6, or 12 months), the name of the branch processing the subscription, and the customer’s
information (name and address).

Each subscription form is created by a single employee of a branch. Within each subscription
form, the names of the subscribed publications are recorded along with the quantity and unit
price of each.

Additionally, to facilitate management and reporting, the center categorizes all publications into
distinct topics (economics, cinema, science, etc.). Each publication belongs to only one topic.
Topic information includes: Topic Code and Topic Name. Each employee belongs to only one
branch and has the following information: Employee ID and Full Name.

18. TOURNAMENT MANAGEMENT

A journalist wants to build a database to track the AFC Cup qualifiers. The information is as
follows:

The qualifiers are divided into several groups; each group is assigned a unique identifier (Group
A, Group B, …). Each group consists of 4 teams competing in a double round-robin
format (home and away – each team plays every other team in the group twice). Each team
competes in only one group.

Information for each team includes: team ID, team name. Each team must register in advance
with the organizers: 1 head coach and a maximum of 22 players. Each player has a jersey
number that distinguishes them from others on the same team. Player information includes:
player ID, player name, date of birth, and playing position (goalkeeper, defender, midfielder,
center-back).

Information for each match (between two teams) includes: match ID, stadium, date, and time of
the match. Stadium information includes: stadium ID, stadium name, and location (e.g., S10,
Thống Nhất Stadium, District 10 - Ho Chi Minh City). After each match, the following data
needs to be recorded: match score, players who scored and the minute each goal was scored,
players who received yellow or red cards.

Sample:

Group D

Team Played Wins Draws Losses Goals Concedes Diff Points

Syria 6 4 2 0 10 2 +8 14
China 6 4 1 1 13 5 +8 13
Vietnam 6 1 2 3 6 11 −5 5
Lebanon 6 0 1 5 2 13 −11 1
Match Date and Time Duration Stadium Weather Temp Attendance
1 14-Jan-2009 07:00 PM 90 My Dinh Stadium Very good 17 C 13000

Match 1 Info:

Vietnam Lebanon
FirstHalf 2 0
SecondHalf 1 1
FullTIme 3 1

19. BEVERAGE MANAGEMENT

Each type of beverage (mineral water, soft drinks, orange juice, sarsi, etc.) has a product code,
belongs to a brand (Pepsi Cola, Fanta, etc.), and has a unit of measure (can, bottle, jug, etc.).
Each type of beverage has a different selling price, current stock quantity, and a reorder
threshold.

Each time a customer makes a purchase, the store will issue an invoice containing the following
information: invoice number, invoice date, customer details (name, address, phone number, tax
code), total amount payable, and the invoice details, which include the type of beverage, quantity
purchased, and the corresponding amount.
At the end of each day, the store checks the remaining stock for each type of beverage. If the
current quantity falls below the reorder threshold, the store will place a new order.

Each brand is supplied by a supplier.

The store places an order through a purchase order, which includes the following information:
order number, order date, types of beverages, quantities ordered and quantities received.
The supplier may apply different prices for each order.

After that, the supplier proceeds with multiple deliveries. For each delivery, the store issues
a goods receipt note, which includes: receipt number, date of receipt, amount payable, the
related purchase order, type of beverage, and quantity received.

20. PHARMACEUTICAL BUSINESS MANAGEMENT

Pharmaceutical Business Management System of a Pharmaceutical Company: The company

employs many staff members. Information about each employee includes: employee ID, full
name, date of birth, place of birth, address, and their assigned position (accountant, medical
representative, etc.).

The company also has many customers. Customer information includes: customer ID, customer
type (hospital, pharmacy store, etc.), customer name, representative’s full name, address, phone
number, fax number, and email.

Medical representatives are responsible for finding customers and introducing products. After a
customer agrees, a contract is created. Contract information includes: contract number, contract
date, customer name, representative's name, address, phone number, employee who created the
contract, contract validity period, and a list of pharmaceutical products: product name, quantity,
and selling price.

Information about pharmaceutical products includes: product ID, product name, unit of
measure, base price, and classification (Class A poison, Class B poison, or non-toxic).

Following the contract, a delivery staff member from the company handles product deliveries.
Products may be delivered in multiple shipments. Each delivery is recorded with a delivery
note, which includes: note number, delivery date, contract number, name of the delivery staff,
and details such as product name, quantity delivered, and expiration date.

One week after each delivery, a company accountant will issue a receipt for that delivery.
Receipt information includes: receipt number, date of issuance, delivery note number, amount
collected, and names of the accountant and delivery staff. The delivery staff will use the receipt
to collect payment from the customer.

REQUIREMENT

1. ERD – Mapping (2 pts)

 2. Database – Table – Primary key – Foreign key in Microsoft SQL Server (1 pts)
3. Checking, default (each student 3 constraints) (1 pts)
- Proposal at least 5 checks, 1 default
- Create checks, default.
- Add data to check the constraints

4. Adjust 1 database at the level of physical (0.5 pts)

- Estimate the large amount of database

- Adjust the files of the database based on estimates
- Create group files, place the data of the tables into the appropriate file group
files
- Proposing index
- Create indexes

5. Create a view (at least 5 views) (1 pts)

- List the user groups

- Proposing views for user groups
- Create views

6. Write the script (at least 2 scripts) (1 pts)

- Proposing scripts
- Write the script

7. Stored Procedure (at least 3 scripts) (1 pts)

- Proposing the Stored Procedure

- Write the Stored Procedure

- Test Stored Procedure

8. Trigger (at least 2 triggers) (1 pts)

- Proposing trigger

- Write the trigger

- Trigger test run

9. User (0.75 pts)

- Proposing the user groups, creating the roles on this proposal

 - Decentralization for the roles

- Create a user (the working position of the user), bring these users into the
appropriate groups

- Test user rights

10. Import - Export- Back Up - Restore: (presenting Step by Step) (0.75 pts)

- Proposing Import, data data situations.

- Prepare data for data data

- Import, Export

- Make backup plans. Proposal implementation.