Question

Develop an EER model for the following situation using the traditional EER notation, the Visio notation, or the subtypes inside supertypes notation, as specified by your instructor: Wally Los Gatos and his partner Henry Chordate have formed a new limited partnership, Fin and Finicky Security Consultants. Fin and Finicky consults with corporations to determine their security needs. You have been hired by Wally and Henry to design a database management system to help them manage their business. Due to a recent increase in business, Fin and Finicky has decided to automate its client tracking system. You and your team have done a preliminary analysis and come up with the following set Walkthr of entities, attributes, and business rules: for pro! Consultant There are two types of consultants: business consultants and technical consultants. Business consultants are contacted by a business in order 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 business consultant are the following: Employee ID (identifier), Name, Address (which is composed of Street, City, State, and Zip Code), Telephone, Date of Birth, Age, Business Experience (which is composed of Number of Years, Type of Business for businesses), and Degrees Received). Attributes of technical consultant are the following: Employee ID (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 businesses that have asked for consulting services. Attributes of customer are Customer ID (identifier), Company Name, Address (which is composed of Street, City, State, and Zip Code), Contact Name, Contact Title, Contact Telephone, Business Type, and Number of Employees.

1) Create Conceptual Schema Diagram

2) Create a fully attributed Loagical Data Model Diagram

3) Create the SQL script that will generate the data model

Answer 1

1. Conceptual Schema Diagram Addrees street city State zip code Business consultant Employee ID Name Address Telephone Date of Birth Business Experience Technical Consultant Employee ID Name Address Telephone Date of Buth Age Techinal skills Degress received Customers customer ID company Name Address Location Contact Name contact Title Contact Telephone Business Type No. 9 Employees Business Experience No. of years Type of Business Degrees Ricind Location castomer ID Locahon ID

ta Model Diagram Data Model Dia 2. Logical Addres street city state (string) (string) (string) Zipcode (int) Business consultant Employee ID (identifier), Name (string) Adoless Telyphone (int) Date of Birth (Date) (int) gusung Experience Age Technical Consultant Employee ID identifier). estring) Business Experience No. of years Lint) Type of Business (string) begrees Ricained estring) Nane Address Telephone (int) Date of Birth ( Date) cint) Technical skills (strings Degress received (string) Customers Customer ID company Name Address Location contact Name contact Title Contact Telephone Business Type No. of Employees identifier) (string) हाम् (string) (string) (string (ints (string) Cint) Location customed Location (identifier) (univarsal)

3. Sql scripts :- > ist script :- SEOLER USE [ABC DAS RSD atabase Name. E Dair CREATE TABLE (ADDRESS] [Testtable AJC [STREET] [VARCHAR(100) (CITY EVARCHAR] (100) [STATE ] [VARCHAR] (100), [ZIPCODE) [INT) 1so), → and VSE CABO GO CREATE TABLE (Business consultant / [Emplogee ID ] [varchar (100)] (Primary key] [name] [varchar cloo)], [Address) ( Forenign key] [varchar(oo)], T Telephone ] [int (255)], [D.O.B) [Date), [Age] Lint (255)], Business Experience] [ Foreign key] [ varchar (100)] USE CABL] cro Create table & Business. Experience de [No of years ] Cint(100)], [Type Of Busi [ Degrees Received] [ varchar (100)]

Go 4th: USE CABC] create table Technical Emplogee ID] [varchar (100) [name ] [varchar (100)], [Addres [Telephone ] Tint (255) [D.O.BT [ Techincal skills ] [varehas (255)], [year 0 char 1100)] [Primary key], [ Address] [Foreign key] [varhar(100) [D.O.B ] [Date], [ Age] I int (255)], col [Degrees Received ] [varchar (255) 5th: USE [ ABC] tomuld [varchar (100)] [Primary key] sarchar(100) Create table customers customuld [varchar (100)] (Prime [ company Name] [vaschen (100)], [Address] [ Foreign ken I Location] [foreigh key] [varchar(100) (contact Name Evarchar(100)] [ contact Title] [ varcher (100)], [contact Telephone] Cint(255)]. [ Business Type] [ varchar (255)], [No.of employa] Cint(255)]) - 6th : USE CABLJ Create table Location ([customer 10] [varchar (100)] [location ID] [varchar (wo) )