Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Steps: Write a script to do the following. (Do this one step at a time, testing the partial results by inspecting the tables in SQLDevcloper.
Steps: Write a script to do the following. (Do this one step at a time, testing the partial results by inspecting the tables in SQLDevcloper. Do not go on the next step until you have the previous step working.) 1. Define the following database, representing employees of an international consulting company, locations that the company has in different cities, and temporary assignments of employees to different locations CONSULTANT(ID, FirstName, LastName, CellPhone) LOCATION(Code, City, State, Country) ASSIGNMENT(ConsultanID. LocationCode. StartDate, EndDate) The consultant IDs should be exactly six characters long, and the cell phone numbers should be exactly ten characters long; you may choose appropriate maximum lengths for the first and last names. The location codes should numbers should be integers with at most two digits, and the states should be exactly two characters long; you may choose the appropriate maximum length for the city names, but country names should be abbreviations with at most three characters. A location may not have a state if it is not in the U.S., but your schema should require that every location indicates both a city and a country. Assignment start and end dates should bc stored as datc objects, not as character strings Define the primary keys as indicated in the schemas above. Also define ConsultantID in ASSIGNMENT to be a forcign key referencing ID in CONSULTANT, and LocationCode in ASSIGNMENT to be a foreign key referencing Code in LOCATION. (This means you will have to create CONSULTANNT and LOCATION before ASSIGNMENT.) In order to avoid conflicts, start your script file with DROP TABLE commands for all three tables (since ASSIGNMENT contains foreign keys, you will have to drop it first). Run your script and look at the columns and constraints of each table to verify that they have been created correctly before going on Now you can set up assignments for some employees... 2. Populate the LOCATION table with codes, cities, states (if needed) and countries of at least four locations of the company. (You can make up the codes, but use actual cities, states, and countries.) Then populate the CONSULTANT table with information for yourself and at least two of your friends as consultants working for the company. (For privacy reasons, don't use your real cell phone numbers.) Look at the data in each table to verify that they have been populated correctly
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