Answered step by step
Verified Expert Solution
Question
1 Approved Answer
this subject is database management, it's my project please solve this i just need the er diagram and creating tabels please Semester I, 2022-2023 TERM
this subject is database management, it's my project please solve this
i just need the er diagram and creating tabels please
Semester I, 2022-2023 TERM PROJECT - HandoutH2 GENERAL ISSUES Throughout the remainder of the semester, you are required to develop an Oracle database application as a term project. The aim of the project is to practice actual database system development. This and other project specification documents will serve as an essential guideline while you are developing your term project. So, please read it carefully and keep it throughout the semester. You can also use these documents as a checklist before submitting a report, i.e., at every major stage of the project, to see if you have satisfied the basic requirements of that particular stage. You should also check announcements on the Blackboard course page regularly to be informed on the project status. The general issues about this term project are listed below: - First, you must form project groups of 3-5 students and e-mail the names to me on Dec 13th,2022 - Keep a record of all your weekly (incremental) work done on your project. This will help you later in the preparation of the final report. - Note that we are assuming that the design of the database system is over, and we are in the stage of the physical implementation of the system. - Do a project demonstration During Dee 27th,2022 Jan 3rt,2023 - Submit a complete project (both a report and development code) by Tuesday Jan 3rd,2023, at 23:59. Mobile Repair Shop Database System MobRepair is a mobile repair shop located in Bahrain. You are to develop a mobile repair management system (MRMS) to help them improve the performance of the current situation and overcome the problems that arise nowadays. Currently, MobRepair uses manual system to record their customer and other business information. A customer drops off his malfunctioning mobile and asks for its repair. The employee records the request and writes a brief description of the problem. When the mobile is fixed the customer is called to pay and collect his/her mobile. Design a database system for a Mobile Repair Shop. When customers bring their mobiles in to be repaired, they make a deposit on the repair job and are given a date to return and take their mobile. Employees then perform repairs on the customers' mobiles based on the repair job and detail the labor costs and the items used for each repair job. This repair shop is specialized only on repair of smart mobile phones. Total costs of each service consists of cost of used parts, cost of specific repair and repairman's hourly rate. When customers return they pay the total cost of the repair job less the deposit, collect a receipt for their payment, and take the repaired mobiles using this payment receipt. ER and database tables: To get you started with of the project, assume that the designer of the database has come up with an appropriate ER diagram. A suggested set of tables are given below. Note that you may need to add more tables or add more fields to have a more logically related functioning database system. For example, we need to keep track of which repair job is done by a given employee in the shop. Repairdob System users: 1. Shop manager 2. Repair employees 3. Admin of the database Give each user the right privileges to do hisher everyday work (aot more and not less). Note that typical users and application administrators aceess the application only through its. APlx. Therefore, they can change the dxea only by inveking package subprograms. Suggested Business Rules: 1. An enployee cannot give discount to customer more than he is allowed by shop manager. 2. A cusloner cannet submit a new repair job if be has already 3 unfinishod and unpaid service jobs. Sugeested Triggers: - Stock_item_low Task of this trigger is set the value of ReorderLow to YES in the items talle whenever the Numlnstock of this item is less 5 . - Change_Log Triger is run immediately after any change to the repairlob table. This activity is recoeded in a special log table along the necessary information (e. . who made the chunge. when, ... ctc.) Sagsested Procedures: - Yearly_report This procedure has parameter for entering the year of the report. This procedure scturns coerplete info about the number of repair jobs, total cost, and average of repair time for each cmployee. - Eaployee_services This procedure has parameter for entering name particular repairman (e.g. "Jaber") This procedure returns complete info about service, which was performed of this employec. The resalt contains date of finish repair, service ID this repair, fail description, particular namber of equipencnt and remark from repairman. It also has name of customer and finally price of service. This procedure gives the shop manager an overview about his employees. - custemaer_services After entering the phone number of customer, this procedure retums the full name of customer, and the total cost of their repair order- in case that they have more than one erder. Functions: - Cost: This function calculates the total cost for service. This cost is calculated from cmployees salary for the repair work, from cost for set of used parts and from cost for service itself. Formula for calculating: finalCost-hours"salary+partsCost where hotirs. time repair in hours (allows fractions) salary. employee salary per hour partsCost............cost for set of used parts - Calculate_discount: This function calculates the amount of discotant to be given to the customer based on discount percentage and cost of service calculated earlier. Sequences: Most of the tables will require their own sequence to automatically generate unique primary keys. Make sure that you create such sequences and later use them in your code in INSERT statements. Tasks to be done Each project team is required to develop MRMS with varying levels of advanced features with varying levels of advanced features using Oracle APEX and PLSQL. Phase 0 (no credit): design the MRMS, draw the ERD, write code to create the tables, the queries, the constraints, indexes, views, relating your design to components of the DBMS. Store all these in a script. Purpose of phase 0 : going through this project phase enables students learn use of DBMS and its components like SQL in building real life application. Phase I: Implement five functionalities of the application using PLSQL procedures and functions exploiting advanced features of the language. 1- Monthly Report. 2- Employee_services 3- Customer Service 4- Cost 5- Calculuate_discount 6. Pay_order Implementation of the database application using Oracle APEX exploiting advanced features. Purpose of phase 1. This project phase allows learning database application developenent with Oracle APEX. The application is intended for different kinds of users: 1. Shop manager 2. Repair employees 3. Admin of the database Note also that these are just examples of operations that they can do. You can add more operations if they make sense in such an environment Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started