This our Database Design & Management Project homework and our team dismiss and I alone I need a help very quickly,

Down here Project Guidelines ; we did until step 3 but we get fall grades so firstly I need a help on step 3 then final report

--------------------------------------------------- Project Guidelines Students (in a group of 5) are requested to design a database for a real-life problem. The groups can be formed by any section members of the course. Database design life cycle contains the following phases:DESIGN STEPS 1. Choosing and proposing a problem (0 points) Form groups Find a real life problem

2. Requirements collection & analysis phase & conceptual schema design Due Date: November 21, 2021 Communicate with customer to identify essential "real world" data that needs to be kept in the database. Give a description of your customer/ contact person. (Where they work/ what they do etc.) Remove redundant, unimportant details, clarify unclear natural language statements, fill remaining gaps in discussions with customer. List the requirements What do the users expect from the developed system? Write your requirements one by one. (give a number to each requirement) Your requirements should be specific to your customers needs. Do not write general requirements. You will get points according to this specialization of requirements. Distinguish data that needs to be stored and operations needed on the data. You can also attach surveys, questionnaires, and interviews forms used to gather information from the customer. You can also attach forms/reports used by customer in the current/existing system. Entities are formed ER/EER data model is designed Late submission will result in 0. No excuses. From this step you will get a STEP-2 grade out of 100. 3. Logical data modelling Due Date: December 17, 2021 ER/EER model is mapped to the logical data model Late submission will result in 0. No excuses. From this step you will get a STEP-3 grade out of 100. 4. Full Project Report Due Date: January 17, 2022 Final version of the Requirements Document and Conceptual DB Design Document and descriptions (Step-2) Final Version of the Logical Model Late submission will result in 0. No excuses. From this part of your report you will get a STEP-4 grade out of 100. 5. Database choice and implementation ( Included in Full Project Report) Tables resulted after Step 4(a) are normalized to BCNF to get Final tables Creating Final tables (relations) with constraints Data loading (at least 10 records per table) Query design (at least 10 meaningful queries along with their results) From this part of your report you will get a STEP-5 grade out of 100. 6. Creating an interface (optional) From this part of your report you will get a STEP-6 grade out of 100. You will get additional bonus points for this step (added to your report grade)- it is optional. Please include the screenshots for four basic operations of your interface: display information (select) and making modification on records (insert, update and delete operations). You should include a screenshot of the interface, also include the related part of the code for interface design.

Individual effort based on interview during final presentation: (50 Points)

7. Project Presentation ( 17-18 January) Prepare a ppt document and upload this file to Moodle (Deadline: January 18, 2022 17:30). Include the following in your presentation: Project Title & Group Members (1st Slide) Brief information about the Project ( including requrements ) Conceptual Design: ER/EER Diagram of your Project (10 pts) Logical Design: ER Diagram mapped to relations (10 pts) Physical Design: (30 pts) o Create tables and insert 3 records per each table. o Write (at least 3) meaningful queries for your system. Please include complex queries: at least one query example for: Display data from multiple tables (join), Query including group-by statement, order-by statement, Subquery

------------------------------------------------------------------------------------------------ OUR DOCUMENT S HERE

Phone Technical Service/Repair Database

Table of Content 1. Introduction -----------------------------------------------------------------------------------1 1.1 Organization table--------------------------------------------------------------------------1 1.2 Database (Organization of system) ----------------------------------------------------2 1.3 Real life problem----------------------------------------------------------------------------2 2. Requirements ---------------------------------------------------------------------------------2 2.1 Interview------------------------------------------------------------------------------------2-3 2.2 Analysis----------------------------------------------------------------------------------------3 2.3 List of Final requirements--------------------------------------------------------------3-4 2.4 Detailed Requirements--------------------------------------------------------------------4 2.5 Design------------------------------------------------------------------------------------------5 2.6 Er diagram------------------------------------------------------------------------------------6 3. Logical design-------------------------------------------------------------------------------7-8 3.1 Er diagram/detailed design--------------------------------------------------------------9

1 Introduction

Company: Our Company FixTech is Phone Technical Service in Turkey and we have lots of branch Ankara, stanbul, zmir, Konya ex. Its important for us customer happiness so we ask our costumer what he\she needs and trouble about. Our company has a department costumer services, IR department, It department, technical services dept. Testing dept., marketing and branding dept., financial and management dept. we have 1000 person in our team we have a big team. So because of this we care about our customer needs and thoughts. We lot of services of company dealing. (Step1 fix) 1.1 Organization table

1.2 Database We have a database holding customer information such as phone model, name, mail, malfunction. When technical service fixes the product, customer receives message to take their fixed products. All data is provided by this database.

1.3 Real life problem There is no such like our database system customer said. Customer identified at next page

2.2 Analysis We talked to our customer, then we listened to his problems, the first of the problems he had was that he was not sure whether there was an original part in the technical service he had done before, he said that there was no database, he said he had a trust problem because there were no records, what dates were made, what was the warranty period, which product. He said that there are no such records, which is why we are planning a system where all the records are kept on the phone where all the records are kept and all important details such as what transactions were made, what date, which part brand model was used. Requirements are

2.3 List of Requirements What do the User (Kubilay) expect from the developed system? This answered with his discussion: System should have database and keep users data System should have product data such like model System should store parts data in the database System should provide messages These are customers specific requirements

2.4 Detailed requirements 1. Customer data a. System should keep data of Customer Customer name, surname, mail, phone will insert to database b. System should keep date and report Date of customers giving product will keep on the database c. Rate/comment Rate and comment service will show on the system (web page, etc.) It will help new customers to have an idea. 2. Information of product a. System should keep data of product. b. All details of products will keep database. 3. Technical report a. System will have technical report such include what is product, how much, what is problem, what has it done etc in the accounting department 4. Transportation a. System will report such include address in the shipment department 5. Receiving message a. shipment department has also text a message or mail to users. Also give product to customer service department.

After the analysis new system could be design

2.5 Design Entities:

Relations: Technical Service has relationship with Customer Service, Shipment D, Accounting D. Customer has relationship Customer Service

However, when the Shipment department receives the product, Technical Service should check it again in order to give it to Customer with Customer Service And Technical Service will send message to customer So all the customer requirements done. 1. Customer data taken by CustomerService to database 2. Product information taken by CustomerService to database 3. Technical report taken by TechnicalService to database 4. Transportation information taken by shipment 5. And Technical Service will send message to customer

2.6 EER diagram Added shipment-CustrService relation to send message about delivery to Customer and fixed relationships with detailed comments

3 Logical design

Entities CustrService : REGULAR ENTITY Customer Service

id : [PK] ATTRIBUTE Type:INTEGER

Number of workers : ATTRIBUTE Type:INTEGER

Experience : [ALLOW NULL] ATTRIBUTE Type:NO DATATYPE

Customer : REGULAR ENTITY

Name : ATTRIBUTE Type:CHAR Length=15

Surname : ATTRIBUTE Type:CHAR Length=20

Mail : ATTRIBUTE Type:CHAR Length=

phone Number : ATTRIBUTE Type:INTEGER

Customer id : [PK] ATTRIBUTE Type:INTEGER

TechnicalServc : REGULAR ENTITY Technical Service

id : [PK] ATTRIBUTE Type:INTEGER

number of workers : ATTRIBUTE Type:INTEGER

Experience : [ALLOW NULL] ATTRIBUTE Type:NO DATATYPE

Shipment D : REGULAR ENTITY Shipment department

id : [PK] ATTRIBUTE Type:INTEGER

Number of workers : ATTRIBUTE Type:INTEGER

Locations : ATTRIBUTE Type:CHAR Length=

Number of Products : ATTRIBUTE Type:INTEGER

Accounting D : REGULAR ENTITY Accounting department

id : [PK] ATTRIBUTE Type:INTEGER

Number of raports : ATTRIBUTE Type:INTEGER

Number of workers : ATTRIBUTE Type:INTEGER

Location of files : ATTRIBUTE Type:CHAR Length=

Relationships info : REGULAR RELATIONSHIP Customer ONE to CustrService ONE Customer information will take by customer service

send : REGULAR RELATIONSHIP CustrService MANY to TechnicalServc MANY Customer service take poducts and give techical service

Report : REGULAR RELATIONSHIP TechnicalServc MANY to Accounting D ONE Tenchinal service report

Report : REGULAR RELATIONSHIP Shipment D MANY to Accounting D ONE Delivery reports

Message : REGULAR RELATIONSHIP Shipment D ONE to CustrService ONE Send information to Customer service to recieve message to customer

Transformation : REGULAR RELATIONSHIP TechnicalServc MANY to Shipment D MANY Trasformation of the product from technical service to other services done by shipment department. Before send shipment department, technical service provide small test report about product (what is the problem which part should change etc).When the product fixed also shipment department return product to technicalService final checking before customer give it.

Customer Service need to customer id and phone number to send massage. Also shipment

3.1 Er diagram/detailed design

---------------------------------------------------------------------------------------------------------------------------------------------------------help please

fix the step 3 and final report

Ceo Project Manager Finance Manager Customer Manager Marketing Manager Presitent IT Services Director of IT tech. (IT Consultant Support Specialist Quality Manangement Help Desk Services By Fixtech Form of Entities Customer Shipment D Cust id name surname mail phone Customer Service id #ofworkers experience id #ofworkers location #ofproducts Accounting D Technical Service id id #ofworkers #ofworkers #ofraports experience Locationoffiles Customer Custr Service - info tid Customer id Name Surname Mail phone Number Number of worki Experience send Technical Servc id number of worke Experience Message Report Transformation Accounting D Shipment D id Number of rapor Number of worki Location of files id Number of worki Locations Number of Produ ---Report-- Customer customer id Customer id Name Surname Mail phone Number -- info 1 phone number Custr Service Hid Number of work Experience send | id Technical Servc Cphone number id number of worke Experience Message customer id id--Report Transformation Accounting D Shipment D id Number of rapor Number of work Location of files id Number of worki Locations Number of Produ --Report- d Ceo Project Manager Finance Manager Customer Manager Marketing Manager Presitent IT Services Director of IT tech. (IT Consultant Support Specialist Quality Manangement Help Desk Services By Fixtech Form of Entities Customer Shipment D Cust id name surname mail phone Customer Service id #ofworkers experience id #ofworkers location #ofproducts Accounting D Technical Service id id #ofworkers #ofworkers #ofraports experience Locationoffiles Customer Custr Service - info tid Customer id Name Surname Mail phone Number Number of worki Experience send Technical Servc id number of worke Experience Message Report Transformation Accounting D Shipment D id Number of rapor Number of worki Location of files id Number of worki Locations Number of Produ ---Report-- Customer customer id Customer id Name Surname Mail phone Number -- info 1 phone number Custr Service Hid Number of work Experience send | id Technical Servc Cphone number id number of worke Experience Message customer id id--Report Transformation Accounting D Shipment D id Number of rapor Number of work Location of files id Number of worki Locations Number of Produ --Report- d