Ahmed and Majid has decided to automate their client tracking system. You and your team have done
Question:
Ahmed and Majid has 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:
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, Dateofbirth, 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: EmployeeID(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 customerid(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 buildingsize.
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.
Q3. Apply normalization (1NF,2NF, and 3NF) of the scenario mentioned in Q2 and justify why relation needs 1NF,2NF, and 3NF or not.(8 marks)
Q4.Create an individual and combined view of the customer, consultant, location, and service of the scenario referred in Q2. {Hint using the concept of views and sequence with the help of joins.}
