Theory DB & File Structures Use Case Realization Explain how the relations and r
ID: 3739433 • Letter: T
Question
Theory DB & File Structures
Use Case Realization
Explain how the relations and relationships are used in EACH of the use cases. Provide the complete SQL statements for every use case.
Use Case: Registration
Actors: Customer
1. Customer goes to the registration page of the website and completes the registration form.
2. The customer will submit the form by clicking the “Submit” button.
3. A confirmation that the form has been submitted will appear.
Use Case: View library of animals
Actors: Customer
1. The customer can view the animals in the database.
2. The customer to select what type of animal they want to view, along with type, breed and age.
3. The customer can submit their request by clicking a submit button.
4. The system will display a confirmation that their request was received.
Use Case: Animals that have been adopted
Actors: Branch Staff
1. Staff clicks on “Adopted” button.
2. All animals will be displayed.
3. Staff can further filter for specific type and breed of animal.
4. Staff further filters male or female.
Use Case: Animals that have been rented
Actors: Branch Staff
1. Staff clicks on “Rented” button.
2. All animals will be displayed.
3. Staff can further filter for specific type and breed of animal.
4. Staff further filter male or female.
Use Case: Animals that have retired
Actors: Branch Staff
1. Staff clicks on “Retired” button.
2. All animals will be displayed.
3. Staff can further filter for specific type and breed of animal.
4. Staff further filter male or female.
5. Selected animals will be displayed.
Use Case: Animals located at a branch
Actors: Manager
1. Manager clicks on “All Animals” button.
2. All animals will be displayed.
3. Manager can further filter for animals by branch.
Use Case: Staff located at a branch
Actors: Manager
1. Manager clicks on “Staff” button.
2. All staff will be displayed.
3. Manager can further filter for by branch.
Use Case: Animals at a branch
Actors: Branch Staff
1. Staff clicks on “Animals” button.
2. All animals will be displayed.
3. Staff can further filter by branch.
Animals have receive medical attention:
1. The Use Case starts when the staff log-on to the system.
2. The system will display the login screen
3. The staff enters a username and password.
4. The system will verify the information.
5. The system will set access permissions.
6. The system will display the main screen.
7. The staff will select to view all animals that have receive any medical attention.
8. The system will display option to save to a file.
Animals have been groomed:
1. The Use Case starts when the staff log-on to the system.
2. The system will display the login screen
3. The staff enters a username and password.
4. The system will verify the information.
5. The system will set access permissions.
6. The system will display the main screen.
7. The staff will select to view all animals that have been groomed.
8. The system will display animal option to save to a file.
Staff can order supplies:
1. The Use Case starts when the staff log-on to the system.
2. The system will display the login screen
3. The staff enters a username and password.
4. The system will verify the information.
5. The system will set access permissions.
6. The system will display the main screen.
7. The staff can select to view the list of suppliers that supply products to their own branch for the animals.
8. The system will display supplier name and supplier telephone.
Use Cases-Supervisors:
1. The Use Case starts when the staff log-on to the system.
2. The system will display the login screen
3. The staff enters a username and password.
4. The system will verify the information.
5. The system will set access permissions.
6. The system will display the main screen.
7. The supervisor can select to view all staff a branch location.
8. The system will display staff name and staffid
1. ANIMAL ENTITY-
AGGREGATE:
How many dogs are at a certain branch
How many cats are at a certain branch
How many of male or female
What is the age of the animal
INSERT:
Staff add another animal
UPDATE:
Staff can update age of animals, adoption dates, rented dates, retired dates.
DELETE:
Staff can delete an animal, no longer in database.
2. BRANCH ENTITY-
AGGREGATE:
How many staff are at a certain branch
Which animals are at a certain branch
INSERT:
Brand new Animal has been added to a certain branch
Brand new Staff has been added to a certain branch
UPDATE:
Animal has moved to different branch
Staff has moved to different branch
DELETE:
(because) Animal is no longer at any branch
(because) Staff is no longer at any branch
3. CHARITY ENTITY-
AGGREGATE:
Count how many charities are associated with a branch
Sum how much a charity donate to a particular branch
INSERT:
Add a new charity to a branch or branches
UPDATE:
Modify how much a charity has donated in total to a particular branch
DELETE:
Remove a charity altogether from the database.
4. CUSTOMER ENTITY-
AGGREGATE:
Count how many customers are in database
Count how many have joined per month
INSERT:
Add a new customer
UPDATE:
Modify telephone number
Modify email address
DELETE:
Remove a customer from the database
5. GROOMER ENTITY-
AGGREGATE:
Count how many groomer are in database
INSERT:
Add a new groomer to the database
UPDATE:
Modify telephone number
Modify first name/last name (if misspelled)
DELETE:
Remove groomer from database
6. MEDICAL ENTITY-
AGGREGATE:
Count how many doctors are associated with a branch or branches.
INSERT:
Add a new doctor to the list
UPDATE:
Modify email address
DELETE:
Remove a doctor from the database
8. STAFF ENTITY-
AGGREGATE:
How many staff are at a particular branch
Max and/or min salaries at a particular branch
How many supervisors are at a particular branch
INSERT:
Add new staff
UPDATE:
Modify salary (see page 170, example 6.38)
DELETE:
Remove staff from database (no longer associated with any branch)
9. SUPPLIES ENTITY-
AGGREGATE:
How much (in dollars) supplies purchased within a month
How many suppliers associated with a particular branch
INSERT:
Add new supplier
Add new purchase
UPDATE:
Modify invoice number
DELETE:
Remove supplier from database.
Remove (bad) invoice from database
Use Case: Animals that have been adopted
Actors: Branch Staff
Adoption:
1. Staff clicks on “Adopted” button.
2. All animals will be displayed.
3. Staff can further filter for specific type and breed of animal.
4. Staff further filter male or female.
5. The actor will click on something
6. Prompt user to enter something
7. Adoption tribute is displayed
8. Use Case starts when the staff log-on to the system.
9. The system will display the login screen
10. The staff enters a username and password.
11. The system will verify the information.
12. The system will set access permissions.
13. The system will display the main screen.
14. The staff will select to view all adopted animals.
15. The system will display option to save to a file.
Explanation / Answer
1.Use Case: Registration
Actors: Customer
The customer will submit the form by clicking the “Submit” button.
SQL Statement:
create table customer(name varchar2(40),number age,number phone,email varchar2(40));
insert into customer values(james,23,283098930,'abcatmy.com');
2.
Use Case: View library of animals
Actors: Customer
1. The customer can view the animals in the database.
2. The customer to select what type of animal they want to view, along with type, breed and age.
SQL
a.select * from animals;
3.select * from animals where type=dog and breed=A and age=5;
Use Case: Animals that have been adopted
Actors: Branch Staff
A. All animals will be displayed.
B Staff can further filter for specific type and breed of animal.
C Staff further filters male or female.
SQL
A.select * from animals;
B.select * from animals where type=dog and breed=german shepherd;
C.select * from animals where type=dog and breed=german shepherd and sex=male;
Use Case: Animals located at a branch
Actors: Manager
1. Manager can further filter for animals by branch.
SQL
select name,type,breed,sex,branch from animals sorted by branch;
ANIMAL ENTITY-
AGGREGATE:
How many dogs are at a certain branch
How many cats are at a certain branch
How many of male or female
What is the age of the animal
select count(no.ofdogs) from animals where branch=melbourne
select count(no.ofcats) from animals where branch=melbourne
select count(*) from animals where sex=male or sex=emale
select name,type,sex, age from animals ;
and all the queries will be written in same manner.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.