Database Design
CmpE 226 Practice Problems
Practice Problem (08)
______
1.Design a database with at least 10-12 of tables or((constraint tables. The constraint tables should use the full power of linear constraints. (For example, the equality constraint x =1 does not use the full power of linear constraints. So don ’t use only equality constraints in your database.)) The tables should contain a reasonable number of tuples or (constraint tuples), not too few and not too many.
2.Prepare traditional class diagram for the following problems showing at least 10 relationships among the following object classes, including associations, aggregations, and generalizations. Show multiplicities in your diagrams.
Your model should have 3-5 attributes and 3-5 operations per class. Use association and role names when needed. As you prepare the diagrams, you may add additional object classes.
3.If you use MLPQ, find at least 4 iconic queries or sequence of iconic queries. Explain what they mean and write them down in the style of the examples in the MLPQ system manual. (Iconic queries are shown in Figure 1 MLPQ Graphical Interface in the MLPQ Specifications, such as Intersection, Union, Difference, Max, Min, etc.)
4. Find at least 4 SQL queries of each of the following kind: Basic, Aggregation, Sets, and Nested. (That is, a total of 16 queries.)
______
GPS based Emergency Services System
Addgroup
Abstract:
For every country, emergency (EMR) medical system is very critical in saving human life. When an emergency erupts, an extra second or two can mean the difference between a life saved and a life lost. Adding GPS tracking to emergency systems would be a valuable support. GPS is a global positioning system based on satellite technology. All EMR centers are equipped with centralized GPS emergency system. When emergency calls are made, the call reaches the nearest EMR center based on the area the call is coming from. The emergency call is immediately updated in the centralized system.
The basic purpose of our GPS based emergency system is to get people rescued as fast as possible.The system can show the entire area on a digital map allowing emergency calls to be pinpointed instantly. Emergency vehicles are displayed as icons so the nearest available unit can be determined.
Domain Description
Domain Description:
The GPS based emergency services system includes a GPS receiving unit that is provided on the emergency vehicle. This unit receives GPS signals from GPS satellites and determines location information of the emergency vehicle. The system also includes a radio-wave transmitter unit that is provided on the emergency vehicle. This unit transmits the emergency vehicle location information by way of an emergency vehicle location signal to a radio-wave receiver unit that is provided in the EMR center. The registered patients are given handsets with GPS-enabled chipsets and transmitters. These handsets are location-aware portable wireless networked devices. When an emergency occurs, he presses the button on the handset. EMR center will receive the signal through the receiver. The system pinpoints the location of both the registered patient and the nearest emergency vehicle on a computer map. The database will be updated every one minute with the current location of emergency vehicles
Major services provided by this system are listed asfollows:
Tracking of nearest emergency vehicles:
All EMR vehicles are tracked by GPS system so as to attend any patients in a short period. The centralized system identifies the nearest vehicle for this patient,directs it to patient and then to the hospital.
Finding Available Medical service:
This system maintains the list of emergency hospitals, physicians, staffs and emergency rooms. This system will help the patient by finding the nearesthospital with available doctors, staffs and emergency rooms.
GPS for critical patients:
Somepatients have more probability to get admitted. For instance, aged people, heart
patients etc. They could register their names in GPS system and get the immediate response in case of emergency. They need to only press the button for calling this emergency system. This system even maintains the list of persons to contact so that the information could be passed to them. Then the same procedure would be followed to get nearest emergency vehicle as well as medical service.
Medical Inventory Database:
This system will direct the emergency vehicles to reach the nearest inventoryand to get needed medicines and first aid equipments like first aid kit, oxygen cylinder .Any m emergency medical center make use of this system.
Area based services:
The whole country is divided into many areas each with a unique area code.
This system keeps track of all areas and their recorded requests to GPS based emergency system. Number of vehicles will be increased for the region with high rate of requests. Demographic information could be collected for each area and number of vehicles for each location could be decided based on its population.
Disaster Management:
The historic data for each area could be collected .For example, some east coastalhave hurricaneduring particular season in every year. It maintains the link to access the corresponding system responsible for each disaster type. More services could be prepared for those areas in that period. Some seasonal diseases also monitored in each year and number of services could be increased accordingly.
Detailed requirements that include use cases
Usecase:
Usecase 1: If a registered patient suddenly gets a heart attack.
Steps:
- A registered patient presses the button of the GPS enabled hand set given to
him.
- The EMR center receives the location of the patient in the form of signal.
- The EMR center tracks the available emergency vehicle nearer to the patient and directs it to the patient.
- It also finds the nearest hospital that has the necessary resources to attend the patient.
- It then directs the emergency vehicle carrying the patient to the hospital.
- It then informs about the patient to their contacts.
In this way, the critical patient is saved from heart attack in very short time.
Usecase 2: If Hurricane occurs in Florida.
Steps:
- EMR center gets the call about the Hurricane.
- It finds the Disaster response Team in that affected area.
- It passes the information about the disaster to the Disaster Response Team and directs it to that area..
- Enter the details of the hurricane in database for future reference.
How about a block diagram
Describe the rest of the entities
Make sure to have 10-12 constraints relations or entities]
Description of Entities and its Attributes:
Entity Type 1:
Entity Type Label: EMRCenter
Entity Type Definition:
Emergency center (EMR center) is a center which receives medical
emergency calls and locates an emergency vehicle nearest to the
emergency site. This tracking of a vehicle is done using a GPS.
Attributes:
Label / Data Type / Constraint / DefinitionemrCenterID / Integer / It is a 8-digit positive value / Each EMR center has a unique ID
areaCode / Integer / areaCode = 5-digit positive value. / The Area code in which the EMR center is located
numOfEmrCalls / Integer / numOfEmrCalls>=0 / The average no of emergency calls per day at a particular EMRCenter.
numOfRegPatients / Integer / numOfRegPatients >=0 / The number of patients registered (patients who need
frequent service)
Entity Type 2:
Entity Type Label: Hospital
Entity Type Description:
The EMR center tracks the nearest available hospital and directs the
emergency vehicle with the patient, to that hospital.
Attributes:
Label / DataType / Constraint / DefinitionhospitalID / Integer / hospitalID is 8-digit positive integer / Each hospital is uniquely identified by a hospital ID.
doctorsOnDuty / Integer / 0<doctorsOnDuty<50 / No of doctors working at the current time
numOfDepartments / Integer / 0<numOfDepartments<20 / Lists the number of departments existing in the hospital.
staffOnDuty / Integer / 0<staffOnDuty<100 / It tells the availability of the staff in the hospital
roomAvailability / Boolean / roomAvailability = yes or no / It indicates if a room is available or not at any given time
x / double / x-axis of the hospital location.
y / double / y-axis of the hospital location.
Entity Type 3:
Entity Type Label: Registered Doctors
Entity Type Definition: This entity is used to provide medical service to a critical patient by calling the appropriate doctor to the Hospital to attend the emergency case. Registered doctors are not always available at Hospitals. Each Doctor is available at different timing and is specialized in different field. This entity is also used to determine the number of doctors who are busy in attending the critical patients,by tracking the information of the doctors.
Attributes:
Label / Datatype / Constraint / DefinitionregDocID / Integer / regDocID is 8-digit positive integer value / It is a value to uniquely identify the doctors of an EMR center
specializationCode / Integer / 4 digits / It determines the specialization of each doctor
availableTime / Time
(hh:mm:ss) / availableTime specified in 24 hours format / It gives the available time of each doctor.
areaCode / Integer / areaCode is 5-digit positive integer. / It specifies the location of the doctor
Entity Type 4:
Entity Type Label: Patient
Entity Type Definition: This entity is used to recognize a person who is in an emergency. He makes a call to the EMRCenter and gives his details.
Attributes:
Label / Datatype / Constraint / Definitionssn / Integer / SSN is 9-digit positive integer / It uniquely identifies every patient
Age / Integer / Maximum 3 digits / Specifies the age group of patient
areaCode / Integer / areaCode is a 5-digit positive integer / It specifies the address of the patient.
typeOfEmergency / Integer / 0<=typeOfEmergency<=5 / It tells what kind of disease the patient is suffering from and hence determines the kind of emergency.
Entity Type 5:
Entity Type Label: DisasterResponseTeam
Entity Type Definition: This entity describes a team which responds to any disaster in an area. When a disaster occurs the EMR centerfinds disaster response team in that area and sends it to the disaster area.
Attribute:
Label / Datatype / Constraint / DefinitiondisasterTeamID / Integer / disasterTeamID is 5-digit positive integer / Every disaster response team has a unique ID
numOfEmployees / Integer / 10<numOfEmployees<=75 / This gives the number of employees currently present in a disaster response team. The EMR center checks this number to find if that response team is sufficient for a particular disaster
areaCode / Integer / areaCode should be 5-digit positive integer / This is the area code in which the disaster response team is located
x / double / x-axis location of the disaster response team
y / double / y-axis location of the disaster
response team
Entity Type 6:
Entity Type Label: RegisteredPatient
Entity Type Definition:
Registered patient is a critical patient who has a particular emergency or disease. He can be an aged person or a patient with heart problem. He is registered to the EMR center. There is a GPS based facility provided, when an emergency occurs the critical patient can just press a button to inform EMR center.
Attributes:
Label / Datatype / Constraint / DefinitionregPatientID / Integer / regPatientID should be 8-digit positive value / This is the ID to uniquely identify each critical patient
problemCode / Integer / Maximum 3 digits / Itdetermines the problem that the patient is suffering. This is also in the database of the EMRCenter so that immediate help corresponding to that problem can be sent.
primaryConNum / integer / primaryConNum is a 10 digit number and should follow the below format:
(XXX) XXX-XXXX / It gives the personal contact number of one of the family member in order to inform if there is any critical situation
x / double / x-axis location of the registered patient.
y / double / y-axis location of the registered patient.
Entity Type 7:
Entity Type Label: EmergencyCall
Entity Type Definition:
This entity shows the emergency call received by EMR center. When emergency calls are made, the call reaches one nearest EMR center based on, which area the call is coming from. The emergency call is also updated in the centralized GPS based emergency system.
Attributes:
Label / DataType / Constraint / DefinitionemergencyCallID / Integer / Each emergency call is uniquely identified with an ID
areaCode / Integer / areaCode is a 5-digit positive integer / This attribute gives the area from which the emergency call
is coming from.
timeStamp / DATETIME / Date and Time should be in the below format:
yy/mm/dt hh:mm:ss / This gives the date and time when the emergency call has been made
typeOfEmergency / Integer / 0<= typeOfEmergency<=5 / It tells the type of emergency associated with the call
Entity Type 8:
Entity Type Label: Area
Entity Type Definition: The entire Country is divided into many areas. Each area has many EMR centers.
Attributes:
Label / Data Type / Constraint / DefinitionareaCode / Integre / areaCode is a 5-digit positive value / Each area is uniquely identified with an area code
population / Integer / 0<=population<=100000 / The population associated with a particular area. According to this the numbers of EMR services are increased in that area.
disasterCode / Integer / 3 digit to uniquely specify probable diaster / This gives the disaster to which that particular area is prone to say for example California is prone to earthquakes. Many South East side states are prone to hurricanes. So that emergency services can be increased in those areas.
diseaseCode / Integer / 3 digit that uniquely specifies a disease. / This gives the disease to which that particular area is prone to.
Entity Type 9:
Entity Type Label: EmergencyVehicle
Entity Type Definition:
This entity is used to provide transportation services to reach Hospital, Inventory and the location of patients or victim. Each Vehicle is equipped with GPS to track its own location as well as to find shortest route for any destination. .Each vehicle is associated with an EMR center. Each vehicle could communicate with its EMR center to find out the required service.
Attributes:
Label / Data Type / Constarint / Defnitioncapacity / Integer / 0<=capacity<=8 / It determines the number of patients to be carried on each vehicle
facility / Integer / 0<facility<=2 / The facility provided in each vehicle(1=well-equipped, 2=basic)
x / Double / X coordinate of the vehicle traced by GPS
y / Double / Y coordinate of the vehicle traced by GPS
isAvailable / Boolean / isAvailable = yes/no / The value confirms the availability of the vehicle
Entity Type 10:
Entity Type Label: Medical Inventory:
Entity Type Definition:
This is used to supply the medicines to the hospital in case of shortage. Each EMR center has a link with a Medical Inventory to get medicines and equipments. It stores the medicine in different category and has the id for each medicine in order to search it easily.
Attributes:
Label / Data Type / Constraint / DefinitioninventoryID / Integer / inventoryID should be 7-digit positive integer value / the key to uniquely identify each inventory .
typeOfInventory / Integer / 0<typeOfInventory<100 / It specifies the category of medicine that is supplied by this inventory
areaCode / Integer / areaCode should be 5-digit positive integre / It gives the location of the inventory
officeHours / Integer / officeHours should be specified in 24-hours format / It represents the open hours of the shop
Entity Type 11:
Entity Type Label:Disease
Entity Type Definition:
This entity gives the details about the number of people affected by various diseases in a particular area.
Attributes:
Label / Data Type / Constraint / DefinitiondieaseCode / Integer / should be 7-digit positive integer value / the key to uniquely specify major disease
isContagious / Boolean / Specify whether disease spreads easily or not
noofPeopleAffected / Integer / It specifies the number of people affected by this disease / The category of the medicine that is present in the medical inventory.
areaCode / Integer / The area in which the disease is spread.
Entity Type 12:
Entity Type Label:Disaster
Entity Type Definition:
This entity gives the details about major disaster that may occur.
Attributes:
Label / Data Type / Constraint / DefinitiondisasterCode / Integer / should be 1-digit positive integer value / the key that uniquely specify the disaster
severityLevel / Integer / 1 digit / Specify the intensity of the disaster
noOfPeopleAffected / Integer / noOfPeopleAffected>0 / It specifies the number of people affected by this disease
areaCode / Integer / The area that is affected by the disaster.