Evaluate MSSQL/MySQL/Oracle features and MSSQL/MySQL/Oracle related products.
-Establish and maintain sound backup and recovery policies and procedures.
-Take care of the Database design and implementation.
-Implement and maintain database security (create and maintain users and roles, assign privileges).
-Database tuning and performance monitoring.
What is a Data Definition Language (DDL)
A data definition language (DDL) is a computer language used to create and modify the structure of database objects in a database.
What is a Data application program?
A database application is a computer program whose primary purpose is entering and retrieving information from a computerized database
Database Interrogation are practical exercises that assess students’ ability to apply theoretical
learning to practical database questions. This assessment will improve student’s ability to design
databases and write SQL queries.
Students will not be assessed on work that the tutor has not seen them produce in class so that
attendance is required as part of this assessment. Students are required to submit the work that
they have completed during the lab session. The details of the lab work and requirements are
provided on the online learning system.
Service match is an upcoming company based in Melbourne. Their primary purpose of business is to provide a platform where clients can access services, needed on a regular basis, easily. These services include heavy goods transportation, domestic and commercial cleaning, landscape and lawn maintenance and design, plumbing works, electrical works, construction and many more. The business process starts with registered clients and service providers. Clients register with Service match using a simple registration form (Appendix A) and can then request for quotes on a service. From the perspective of the company, the clients are mainly classified as corporate and individual clients. Corporate clients are those who require services for professional purposes and individual clients are those who require services for personal or household purposes. For each of the registered clients, the company keeps record of their name, address, phone number and email address. Apart from this basic information, the company also keeps track of property ownership circumstances of the individual clients. They want to know whether a client is a property owner, or a tenant in a rental property. Service match uses the information for promotional purposes. For the corporate clients, the company also keeps track of the business information such as name of the corporation and business area. Similarly, service providers or businesses registers (Appendix B) with the company with their details and are approached by the company if there is any suitable work for them. For the registered businesses, the contact person name, phone number, email address, business address, suburb, and ABN number are recorded. Service match do not allow a business to register with them unless they have a valid Australian Business Number. Moreover, the businesses are mainly of two categories, i.e. corporate businesses, who are registered as a company, and freelancers who are registered as single person service providers. Service match keeps track of these two categories, as they offer the some of the freelancers, free career seminars for continuous service improvement and future business expansion. When a registered client requires assistance from Service match, the person creates a job request by filling up a job request form (Appendix C). Jobs can also be of two main categories. Casual jobs are only created by individual clients and are one-time events. On the other hand, contractual jobs are only created by corporate clients and have specific contract start and end date. Each job is assigned a unique job id, a job description, a level of urgency. Service match maintains a collection of suburbs. Each suburb has a name and postcode. They also store information about adjacent suburbs. A suburb can have more than one adjacent suburb. Every created job is related to one suburb. Additionally, each job belongs to one of the predefined job industries. Each industry has a unique id and a title. When a business (both corporate and freelancer) registers, they are also required to select one or more industries to be affiliated with. Once a job is created, based on the suburb, the nearby businesses who are affiliated with the same job industry, are notified. The interested businesses provide quotes for each job requests, based on the job description and urgency. A job can receive many quotes from different businesses. Similarly, a business can quote many jobs at the same time. The amount of money quoted by a business for a job, is also recorded. Once the client evaluates all the quotes, one business is assigned the job, and the information is recorded. The payment system of Service match is a very simple one. The invoice is generated against each job that has a unique invoice number and the amount to be paid by the client to the business. One job can have multiple invoices associated with it. The company further keeps track of the trade unions that represent some of the industries listed by Service match. The officials believe that if they have listed unions’ contact details, it might be helpful to communicate easily with the businesses on a mass level. A trade union can represent more than one industry, for example, Mowers Union represents Landscaping and Mowing industries. However, each industry is represented by at most one trade union. Service match allows the freelancers to become a part of elite members’ club. Additionally, it also allows the trade unions to become elite members. The perks of being an elite member is to get invitation to different career seminars which are free and beneficial for businesses to thrive. An elite member is a registered freelancer or a union representative but not all freelancers or unions are elite members unless they have registered to be one. The invitation to different career seminars is sent to the elite members periodically along with the seminar details, i.e. title, date and time, venue and so on. Many members can attend a seminar, but the company does not allow a seminar to take place if there are less than 5 attendees. This rule helps justifying the cost of arranging an expensive seminar. As the company is a newly formed enterprise, they do not have an online system yet, which is making things a lot harder for the employees. So, before moving to an online management system Service match wants a relational database system that can cater for their entire business process and emerging volume of data.
You are required to develop an EER model for the above problem description. The EER should contain all necessary information such as entities, attributes, primary keys, relationships (including specialization/union if any), cardinalities, and participation (including (min, max)). Any assumptions should also be stated clearly. If assumptions are made, it is important to make sure that the assumptions reflect possible real practice for a similar business and do not contradict with the problem description above. Assignment should be typed, not written/drawn by hand. Use any software to draw figures in your assignment. However, make sure to follow the notations introduced in this subject.
Q.No.2.
Ahmed and Majid have decided to automate their client tracking system. You and your team have done some preliminary analysis and come up with the following entities, attributes and business rules: (8 marks)
Consultant
There are two types of consultants: business consultants and technical consultants. Business consultants are contacted by a business to first determine security needs and provide an estimate for the actual services to be performed. Technical consultants perform services according to the specifications developed by the business consultants. Attributes of a business consultant are the following: employee ID(Identifier), Name, Address (which is composed of street, city, state and zip code), Telephone, Antibirth, age, business experience (which is composed of a number of years, type of business(or businesses), and Degrees Received).
Attributes of a technical consultant are the following: Employed(identifier), Name, Address (which is composed of street, city, state, and zip code), telephone, date of birth, age, technical skills, and degrees received.
Customer
Customers are business that have asked for consulting services. Attributes of customer are customer(identifier), company name, address (which is composed of street, city, state and zipcode), Contactname, ContactTitle, ContactTelephone, BusinessType, and number of employees.
Location
Customers can have multiple locations, Attributes of location are customerid(identifier), locationid (which is unique only for each customerid), address (which is composed of street, city, state and zipcode), telephone and building size.
Service
A security service is performed for a customer at one or more locations. before services are performed, an estimate is prepared. Attributes of service are service id (identifier), description, cost, coverage, and clearance Required.
Additional Business Rules
In addition to the entities outlined above, the following information will need to be stored in tables and should be shown in the model. these may be entities, but they also reflect a relationship between more than one entity.
· Estimates, which have characteristics of Date, Amount, Business Consultant, Services, Customer.
· Services Performed, which have characteristics of Date, Amount, Technical Consultant, Services, Customer
To construct an EER Diagram, you may assume the following:
A customer can have many consultants providing many services. We wish to track both actual services performed as well as services offered. Therefore, there should be two relationships between customer, services, and consultant, one to show services performed and one to show services offered as part of the estimate.
What aggregation feature is missing from the Enhanced Entity-Relationship (EER) model? How can the EER model be further enhanced to support it?
Case Study: A database system for managing COVID19 patient cases
Due to the coronavirus pandemic, a state government health agency urgently needs to build a
database to record relevant data. The management of the health agency has approached you to build
a relational database that will be used as the most critical backend software component in their
application system so that the relevant business data will be recorded appropriately and further
information queries will be processed efficiently. The proposed database system requires efficiently
to record and track all patients, doctors, hospitals, and relevant information on diagnosis, treatments,
and virus tests, etc. Consider the following user requirements carefully and design a database
conceptual schema (i.e. E-R model) to support such an application.
A patient refers to a person who has been infected in fact by the coronavirus. The symptoms of the
patient could appear in mild condition or serious condition. Therefore, the patients can be roughly
categorized into two types – mild condition patients who can stay home for a self-isolation process,
and serious condition patients who need treatments in a hospital. The patient’s full name, gender,
residential address, birth date, and contact phone must be correctly recorded. When a patient comes
to a fever clinic of a hospital, firstly the patient is diagnosed by doctors. Then depending on the
diagnosis outcome and symptoms, the patient either is suggested to return home for isolation or is
arranged to be treated in the hospital. Even for home isolation, the case of the patient is still being
managed by the hospital. The patient can contact the hospital at any time for a further check or
assistance if the patient feels necessary or condition changed. A patient generally undergoes several
times test of viruses. Each time the virus test must be recorded with the testID, time & date, method,
result (positive or negative), the technician name, and the name of the testing site. The developing
database system will record relevant information on diagnosis and treatment processes. The database
system also stores the details of each doctor including their names, birth dates, genders, phones,
specialistic areas, as well as their supervisor names. Besides, information on each hospital in this state
is essential in this database system. This should include the hospital name, address, contact phone,
the name of the managing director, and the number of available respiratory machines.