The Blue Mountains Financial Services company provides financial services (banking, advisory, tax
consulting, auditing, insurance, wealth management etc.) to customers.
The company has contracted an outsourcing IT company to develop an Information System.
The System database should satisfy the following business rules:
• The company wants to keep employees and customers in the database under one entity
Person. Each Person has at least one Bank Account. The Person attributes are: pID, pName,
pAddress. The BankAccount attributes are: baID, baName, baType, baBalance.
• Each Person belongs to a category: Seller, Customer or Representative. Customer can also
be Representative, i.e. he/she can provide services to customers.
• Seller can be one of the categories: category 1 or category 2.
• Customer can be one of two categories: private or corporate.
• Representative can be employed as casual or on a contract.
• Seller/Representative provide Services.
• Seller/Representative can have more than one Customer.
• Seller/Representative signs an Agreement with a Customer.
• Agreement contains at least one of the Services provided by the company.
• Agreement can be one of two types: a contract or an order.
• Seller/Representative issues Invoice to Customer for services
• Each Invoice contains Invoice Items.
• Each Invoice Item is one of the Services
The Papa Car Service & Repair Centres are owned by the Silent Car Dealership;
Papa services and repairs only silent cars. Three Papa centres provide service and
repair for the entire state.
Each of the three centres is independently managed and operated by a shop
manager, a receptionist, and at least eight mechanics. Each centre maintains a fully
stocked parts inventory.
Each centre also maintains a manual file system in which each car’s maintenance
history is kept: repairs made, parts used, costs, service dates, owner, and so on. Files
are also kept to track inventory, purchasing, billing, employees’ hours, and payroll.
You have been contacted by one of the centre’s managers to design and implement a
computerized database system. Given the preceding information, do the following:
a. What sequence of activities that are most appropriate will you take to design
and implement a computerized database system.
b. Describe the various modules that you believe the system should include.
c. How will a data dictionary help you develop the system? Give examples.
d. What general (system) recommendations might you make to the shop
manager? For example, if the system will be integrated, what modules will be
integrated? What benefits would be derived from such an integrated system?
Include several general recommendations.
e. What is the best approach to conceptual database design? Why?
f. Name and describe at least four reports the system should have. Explain their
use. Who will use those reports?
Identify all the important concepts represented in the library database case study described below. In particular, identify the abstractions of classification (entity types and relationship types), aggregation, identification, and specialization/generalization. Specify (min, max) cardinality constraints whenever possible. List details that will affect the eventual design but that have no bearing on the conceptual design. List the semantic constraints separately. Draw an EER diagram of the library database. Problem 03
Week 8 Assignment 2: Create an Redispose a local college has tasked you to develop a database that will keep track of students and the courses that they have taken. In addition to tracking the students and courses, the client wants the database to keep track of the instructors teaching each of the courses.
Database Design Diagram
Use any drawing package or any other application you know such as Visio, Word, PowerPoint, or another tool to create the Database Design Diagram. Otherwise, you may draw the diagram by hand with pen and paper. If you draw the diagram by hand, then take one or more pictures of the diagram with your cell phone or laptop camera, place the images into a Word document, and submit the document. Otherwise, if you use software to create the Database Design Diagram, then take one or more screen captures of the diagram, place the screen captures into a Word document, and submit the document. Submissions that are not in .doc or .docx format will not be graded.
For each student, the college needs to track the student ID, student names, addresses, start date, phone numbers and types (such as mobile, home, work), email addresses and types (such as personal, school, work), gender, and birth date.
For each course, the college needs to track the course ID, course name, department, quarters offered, sections, and instructor teaching each course.
For each instructor, the college needs to track the instructor ID, name, address, phone numbers and types (such as mobile, home, work), email addresses and types (such as personal, school, work), gender, birth date, and the courses that each instructor is qualified to teach. Additionally, the college needs to track the final grades for each student after each course has completed.
1 What four main types of actions involve databases? Briefly discuss each. 2 Discuss the differences between database systems and information retrieval systems. 3 What is the difference between logical data independence and physical data independence? Which one is harder to achieve? Why? 4 What is the difference between the two-tier and three-tier client/server architectures? 5 Discuss the capabilities that should be provided by a DBMS. 6 What is the difference between a database schema and a database state? 7 Define the following terms: entity, attribute, attribute value, relationship instance, composite attribute, multi valued attribute, derived attribute, complex attribute, key attribute, value set (domain). 8 Why are duplicate tuples not allowed in a relation? 9 What is normalization?
Case Study Scenario:
The Unipharm chain of pharmacies has offered to give you a free lifetime supply of medicine if you
design its database. Unipharm is a small medium enterprise (SME) in terms of company structure
and has four (4) pharmacies in the Auckland region. Being an SME, they would like to keep their
costs down but also to have an efficient solution to manage their back-office administration. Here
is some more information to help you out with the case study:
• Patients are identified by a Social Security Number (SSN), and their names, addresses, and ages must be recorded.
• Doctors are identified by an SSN. For each doctor, the name, specialty, and years of experience must be recorded.
• Each pharmaceutical company is identified by name and has a phone number.
• For each drug, the trade name and formula must be recorded. Each drug is sold by a given
pharmaceutical company, and the trade name identifies a drug uniquely from among the
products of that company. If a pharmaceutical company is deleted, you need not keep track
of its products any longer.
• Each pharmacy has a name, address, and phone number.
• Every patient has a primary physician. Every doctor has at least one patient.
• Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another.
• Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several
patients, and a patient could obtain prescriptions from several doctors. Each prescription
has a date and a quantity associated with it. You can assume that, if a doctor prescribes the
same drug for the same patient more than once, only the last such prescription needs to be
stored.
• Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical
company can contract with several pharmacies, and a pharmacy can contract with several
pharmaceutical companies. For each contract, you have to store a start date, an end date,
and the text of the contract.
• Pharmacies appoint a supervisor for each contract. There must always be a supervisor for each contract, but the contract supervisor can change over the lifetime of the contract.
1. As the Database Administrator (DBA) write down the relational schema for at least 5 tables of the database in terms of the company’s structure.
2. Draw an ER diagram that captures the preceding information. Identify any constraints not captured by the ER diagram. 3. How would your design change if each drug must be sold at a fixed price by all pharmacies?
4. As a part of the Unipharm change management plan how would you administer changes if the design requirements are as follows:
If a doctor prescribes the same drug for the same patient more than once, several such prescriptions may have to be stored.
5. Unipharm is concerned about weekly data backup. Formulate and briefly describe a data backup plan.
Each of the 4 Unipharm pharmacies have a Pharmacy Manager, a data entry operator,
an accountant, 2 pharmacists and database administrator. Keeping this company
hierarchy in mind please answer the following questions-
6. Describe access privileges level of the five (5) employee types of Unipharm in the database? and who is the person deciding the access privilege levels?
7. Being the DBA, you have to provide different access levels to the employees; mention any three (3) access related.
8. Recently 100 tables were updated in the database after new products came in. As
a Database Administrator, what tasks will you perform to ensure consistency of the
database?
9. DBMS is a highly complex system with hundreds of transactions being executed
every second. Unipharm is concerned if it fails or crashes in the middle of
transactions, it is expected that the system would follow some sort of technique to
recover lost data. Discuss categories of failures and methods to formulate a
recovery plan.