Concept explainers
a)
DBLC (
- DBLC is a sequential process which is used to trace the history of the application that is present within the
information system . - The history of the application can be traced from the beginning till its last even though being outdated.
- DBLC is concurrent to SDLC because the database represents the core of the information system.
- It is composed of six phases:
- Initial study:
- Examining the operations of the current system.
- Design:
- It focuses on designing the database model which satisfies both operation and objectives of the company.
- Implementation and loading:
- It is a process of designing the specifications.
- Testing and evaluation:
- Ensure the database performance.
- Operation:
- The process that is designed to make it operational.
- Maintenance and evolution:
- Performs routine maintenance activity within the database.
- Initial study:
a)
Explanation of Solution
Order in which the design of the database made for the ABC car service and repair Centers:
The correct sequence of process are based on the phases of DBLC, here the sequence are arranged based on the main three phase namely analysis, design, and implementation:
The order that is sorted based on the phases of DBLC:
Analysis:
- The shop manager gets interviewed.
- The
mechanic of the shop is interviewed. - Operations of the company are obtained in the form of general description.
- For every system process descriptions are created.
Design:
- E-R diagram is used to create a conceptual model.
- The system flow charts and diagrams for data flow are created.
- The conceptual model gets normalized.
Implementation:
- The table structure gets created.
- The contents are loaded into the database.
- Application programs are created.
- The system gets tested.
Correct order:
Correct order | Description |
7 | Normalize the conceptual model |
3 | Obtain general description of the company operations |
9 | Load the database |
4 | Create a description of each system process |
11 | Test the system |
6 | Draw the data flow diagram and system flow chart |
5 | Create a conceptual model using ER diagram |
10 | Create the application program |
2 | Interview the merchants |
8 | Create the table structure |
1 | Interview the shop manager |
b)
Explanation of Solution
The main modules that are needed to be included for the development of the system:
- Inventory.
- Maintains the inventory of the system,
- Payroll.
- Payroll information gets processed
- Work order.
- Processing details of the work order.
- Customer.
- Payments and receipt of the work order made are maintained.
Sub-modules of the main modules include:
Inventory
- Parts
- Parts those are used or maintained or purchased.
- Purchasing
- Holds the purchase history.
Payroll
- Employee payroll
- The payroll for the employees gets processed.
Work order
- Maintenance
- Histories of maintenance are recorded.
- Work order
- Maintenance histories that are made to the card are recorded.
Customer
- Billing
- Bill of the work order are being tracked
- Payment
- Payments received are being recorded.
Structure of the module design:
The structure of the entire system will be as shown below:
ABC car service and Repair Information system
- Inventory
- Parts
- purchasing
- Payroll
- Payroll of Employee
- Work order
- Maintenance
- Work order
- Customer
- Billing
- Payment.
c)
Data Dictionary:
The collection of description of data objects and a set of information that describes the content, format and structure of a database is called Data Dictionary. It is also used to control access in database and describe the relationship between the elements.
Function of data dictionary in database design:
The functions of data dictionary in the database design are listed below:
- The contents of data dictionary are used for verifying the components of database design such as entities, attributes and relationships that exists between them.
- Database design is used by the designer to check for their homonyms and synonyms.
- It also verifies the entities and attributes created can be able to support all the queries in required way along with report requirements.
c)
Explanation of Solution
Usage of data dictionary in the development of the system:
The usage of data dictionary in the development of the system:
- They ensure the contents of the data dictionary provide consistency across the modules that are developed.
- They are used to evaluate the ability of the system and generating the reports that are required.
- They validate the relationship that is represented.
- They validate the support of the attributes in the report generation.
Example:
The data that are created needs to be used in a proper way, the billing system utilizes the records of the payment and receipt of the customer and this type of data ensures a proper report generation is made for the history of maintenance along with their payment and receipt history.
d)
Explanation of Solution
Recommendation that can be given to the shop manager:
- The role of the designer is to provide solutions to the problems that are identified during the initial stage.
- The system that is developed is subjected to both external and internal constraints.
- Consider the following example:
- Consider the designing work that is made the system developed must concentrate and prioritize the development of module that will bring the benefits at the stated time and with the given budget.
- The time required in the development of the system need not to be spends on identifying the relationships.
- The modular system that is developed needs to be developed in a way that any future improvements that are necessary can be merged to it whenever required.
- Consider the following example:
- Consider the management is interested in integrating the service stations of all the states for providing service statewide.
- This kind of integration can provide greater benefits.
- By this the car history that is being serviced will be available at any location; through this the data maintenance history of the car will be available to the mechanic who services the car. This enables the service being offered in an efficient way.
- Thus, the order of service can be placed at location.
e)
Explanation of Solution
Best approach for the conceptual database design:
- The best approach to provide the conceptual database design based the given nature of the business would be centralized and top down approach.
Top down approach:
- The design approach begins by identifying the data sets and by defining the data elements for each of the sets.
- This process takes place by identifying the available entity type that are different and attributes of each entity gets defined.
- This type of design approach is well suited for the database designers who deal with the complex and large databases.
Centralized approach:
- This type of database design is suited for small organization
- They have relatively small and simple databases with entire view can be identified easily.
- The process of the entire design is subjected to designers.
- The validation and completion of this design is completely based on the single conceptual design.
Reason:
- The design that is designed by the designer should ensure that any changes or enhancements that are needed to make are being available at the flexible rate.
- Any future integration that are requested or made needs to be accepted by the
system design .
e)
Explanation of Solution
List of four reports that system can contain:
The below are the list of four reports that system can contain and they are:
- Report on monthly activity.
- It contains the summary of service by category which is categorized based on branch and by month.
- It acts as a base for forecasting personnel and maintains the stock requirements for each branch and period.
- Report on the mechanic summary sheet.
- It contains record of number of hours worked by the mechanic.
- This kind of data will be greatly helpful for the payroll processing.
- This is used for maintenance personnel for making scheduling activity.
- Report about monthly inventory.
- It contains records about the summary of inventory.
- The information about vendors, inventory draw down reorder points and so on.
- It has many purposes in the inventory management.
- Report about the customer activity.
- It contains activities of customers that breakdowns of the customer based on location, activity scheduled for maintenance, balances and credits available.
- They could offer services based on demand by forecasting promotional mails about the maintenance schedule as remainder and provide special kind of requirements based on the customer needs.
Want to see more full solutions like this?
Chapter 9 Solutions
Database Systems: Design, Implementation, & Management
- The Papa Car Service & Repair Centers are owned by the Silent Car Dealership; Papa services and repairs only silent cars. Three Papa centers provide service and repair for the entire state. Each of the three centers is independently managed and operated by a shop manager, a receptionist, and at least eight mechanics. Each center maintains a fully stocked parts inventory. Each center 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 center’s managers to design and implement a computerized database system. Given the preceding information, do the following: What general (system) recommendations might you make to the shopmanager? For example, if the system will be integrated, what modules will be integrated? What benefits would be derived from such an…arrow_forwardThe Papa Car Service & Repair Centers are owned by the Silent Car Dealership; Papa services and repairs only silent cars. Three Papa centers provide service and repair for the entire state. Each of the three centers is independently managed and operated by a shop manager, a receptionist, and at least eight mechanics. Each center maintains a fully stocked parts inventory. Each center 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 center’s managers to design and implement a computerized database system. Given the preceding information, do the following: Name and describe at least four reports the system should have. Explain their use. Who will use those reports?arrow_forwardThe Papa Car Service & Repair Centers are owned by the Silent Car Dealership;Papa services and repairs only silent cars. Three Papa centers provide service and repair for the entire state.Each of the three centers is independently managed and operated by a shop manager, a receptionist, and at least eight mechanics. Each center maintains a fully stocked parts inventory.Each center 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 center’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…arrow_forward
- The Papa Car Service & Repair Centers are owned by the Silent Car Dealership; Papa services and repairs only silent cars. Three Papa centers provide service and repair for the entire state. Each of the three centers is independently managed and operated by a shop manager, a receptionist, and at least eight mechanics. Each center maintains a fully stocked parts inventory. Each center 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 center’s managers to design and implement a computerized database system. Given the preceding information, do the following: How will a data dictionary help you develop the system? Give examples.arrow_forwardpls help me : Draw an ER diagram according to the following requirements:Database to manage daily medical examination and treatment in the hospital (not interested in treating inpatients):- The hospital has many doctors and nurses from different departments. Each doctor and nurse belongs to only one department- The patient when he comes to the doctor will be created a patient record if it is the first visit- Each visit will be made a medical record and assigned an initial clinic. Each visit is attended by a doctor and an assistant nurse.- The doctor may ask the patient to perform tests, x-rays, endoscopy, ....- After the results are available, the patient returns to the original clinic for the doctor to read the results and prescribe medicine if any- Patients can buy medicine in the hospital pharmacy or buy it outsidearrow_forwardConsider the following dimensions, dimension attributes, and dimension sizes for Farm Coop-Insurance: Member(member_ID, Name, Address). On average, there are two members for each policy and item covered by the policy. InsuredItem(Item_ID, Description, Coverage_Type). There is an average of ten covered items per policy. CoopOffice(Office_ID, Address, Manager_name). Each policy is registered and managed by only one office of the cooperative. Policy(Policy_ID, Type). The company has approximately one million policies at the present time. Approximately five percent of these policies experience some change each month. Period(Date_Key, Fiscal_Period). The length of the fiscal period is one month. The decision system reports are supposed to be based on five years of data. Claim(Claim_ID, Claim_Description, Claim_Type). The facts to be recorded for each combination of these dimensions are Policy_Premium, Deductible, and Monthly_Claim_Total. Design a star schema for this problem. Using the…arrow_forward
- The management of KimTay Pet Supplies (a supplier of pet supplies, food, and accessories located in Cody, Wyoming) has determined that the company’s recent growth no longer makes it feasible to maintain customer, invoice, and inventory data using its manual systems. In addition, KimTay Pet Supplies wants to build an Internet presence. With the data stored in a database, management will be able to ensure that the data is up-to-date and more accurate than in the current manual systems. In addition, managers will be able to obtain answers to their questions concerning the data in the database easily and quickly, with the option of producing a variety of useful reports. List the item ID, description, and on-hand value of each item whose number of units on hand is more than the average number of units on hand for all items. Use a subquery.arrow_forwardThe management of KimTay Pet Supplies (a supplier of pet supplies, food, and accessories located in Cody, Wyoming) has determined that the company’s recent growth no longer makes it feasible to maintain customer, invoice, and inventory data using its manual systems. In addition, KimTay Pet Supplies wants to build an Internet presence. With the data stored in a database, management will be able to ensure that the data is up-to-date and more accurate than in the current manual systems. In addition, managers will be able to obtain answers to their questions concerning the data in the database easily and quickly, with the option of producing a variety of useful reports. The CUSTOMER table maintains information about each customer, such as their ID, first and last name, address, balance, and credit limit. CUSTOMER table In the INVOICES table contains information about each invoice, such as the invoice number, date, and the customer being invoiced. INVOICES table The INVOICE_LINE table…arrow_forwardConsider the following College and perform the task that follows.Ocean Sound College, a liberal arts college located on the West Coast, provides dormitory housing for approximately half of its students. Students who choose not to live on campus, or who do not get assigned dormitory space, must find housing in the surrounding community. Currently, the housing office uses a cumbersome system of multiple binders to keep track of housing opportunities for students, faculty, and staff of the college. Housing listings are for college-owned housing, housing owned by private parties including landlords and individuals looking for roommates. The current system is difficult to maintain and keep up-to-date. The new college president approved a request to fund the development of a system to provide easier access and maintenance of listings. You are the systems analyst assigned from the college IT department to conduct the initial investigation into the feasibility of such a system. The preliminary…arrow_forward
- Air-india want to create database to stores details about an airline’s fleet, flights, and seat bookings. Again, it’s a hugely simplified version of what a real airline would use, but the principles are the same. Draw an ER diagram based on the information given.Consider the following requirements list: • The airline has one or more airplanes.• An airplane has a model number, a unique registration number, and the capacity to take at most 100 passengers.• An airplane flight has a unique flight number, a departure airport, a destination airport, a departure date and time, and an arrival date and time.• Each flight is carried out by a single airplane.• A passenger has given names, a surname, and a unique email address.• A passenger can book at most 5 seats on a flight.[7](b) Convert the diagram 1(a) in relational model.arrow_forwardNote: Terry’s phone is not available in the evidence. However, several files that originated from the phone exist somewhere in the evidence. Can you find them? Are they related to the case? 2009-M57-Patents The 2009-M57-Patents scenario tracks the first four weeks of the 13corporate history of the M57 Patents company. The company started operation on Friday, November 13th, 2009, and ceased operation on Saturday, December 12, 2009. As might be imagined in the business of outsourced patent searching, lots of other activities were going on at M57-Patents.arrow_forwardMake an ER Diagram based on this information below NYU needs a new system to manage reservations of study rooms at the ULibrary (UL) . Data requirements for this system are listed as follow: Study rooms are identified by a unique room number (Ex. UL-231). Also, capacity (number of seats in the room), type ('Lab', 'Quiet', 'General'), and status ('Available', 'Reserved' , 'Unavailable') for each room must be stored in the system. The default status for a study room is 'Available'. Some rooms have a projector and others don't. This information is also required to be stored. Students are identified in this system by their unique Bobcat ID. Also their unique NYU student email, name (composed by first, middle, and last name), major, and academic level ('Freshman', 'Sophomore', 'Junior', 'Senior', 'Grad Masters', 'Grad PhD') must be stored for each student. All NYU students will be added to this system no matter they reserve a room or not. Students will be able to create reservations…arrow_forward
- Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill EducationStarting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSONDigital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
- C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSONDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningProgrammable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education