Create the CUSTOMER, CONSULTING_PROJECT, and DAILY_PROJECT_HOURS tables. Create
ID: 3698745 • Letter: C
Question
Create the CUSTOMER, CONSULTING_PROJECT, and DAILY_PROJECT_HOURS tables.
Create the primary and foreign keys in each table, and the relationships between the tables.
Add at least three rows of data in each table.
You may use either the Access GUI or SQL for any parts of the database.
Customer Seminar Seminarld SeminarDate SeminarTime Location SeminarTitle LastName FirstName Phone Steet Address City State Zip Contact Emailld ContactDate Contactno ContactType Consulting Project Projectld ProjectName Description StartDate EndDate TotalHours TotalCost TotalHoursActual TotalCostActual Daily Project Projectid Workdate HoursWorked DescriptionExplanation / Answer
if you have any doubts, please give me comment...
CREATE TABLE Seminar(
SeminarId INTEGER NOT NULL PRIMARY KEY,
SeminarDate DATE,
SeminarTime TIME,
`Location` VARCHAR(100),
SeminarTitle VARCHAR(255)
);
CREATE TABLE Customer(
LastName VARCHAR(100),
FirstName VARCHAR(100),
Phone CHAR(13),
StreetAddress VARCHAR(100),
City VARCHAR(100),
`State` VARCHAR(100),
Zip CHAR(6),
PRIMARY KEY(LastName, FirstName)
);
CREATE TABLE Contact(
EmailId VARCHAR(100) NOT NULL PRIMARY KEY,
ContactDate DATE,
Contactno CHAR(13),
ContatctType VARCHAR(20),
SeminarId INTEGER,
LastName VARCHAR(100),
FirstName VARCHAR(100),
FOREIGN KEY(SeminarId) REFERENCES Seminar(SeminarId),
FOREIGN KEY(LastName, FirstName) REFERENCES Customer(LastName, FirstName)
);
CREATE TABLE ConsultingProject(
ProjectId INTEGER NOT NULL PRIMARY KEY,
ProjectName VARCHAR(255),
`Description` TEXT,
StartDate DATE,
EndDate DATE,
TotalHours INTEGER,
TotalCost REAL(10,2),
TotalHoursActual INTEGER,
TotalCostActual REAL(10,2),
LastName VARCHAR(100),
FirstName VARCHAR(100),
FOREIGN KEY(LastName, FirstName) REFERENCES Customer(LastName, FirstName)
);
CREATE TABLE DialyProject(
ProjectId INTEGER NOT NULL PRIMARY KEY,
Workdate DATE,
HoursWorked INTEGER,
`Description` VARCHAR(255)
);
CREATE TABLE CUST_WORK_ON_DIALY_PROJ(
LastName VARCHAR(100),
FirstName VARCHAR(100),
ProjectId INTEGER,
PRIMARY KEY(LastName, FirstName, ProjectId),
FOREIGN KEY(LastName, FirstName) REFERENCES Customer(LastName, FirstName)
FOREIGN KEY(ProjectId) REFERENCES DialyProject(ProjectId)
);
CREATE TABLE CUST_GIVE_SEMINAR(
SeminarId INTEGER,
LastName VARCHAR(100),
FirstName VARCHAR(100),
FOREIGN KEY(LastName, FirstName) REFERENCES Customer(LastName, FirstName)
FOREIGN KEY(ProjectId) REFERENCES Customer(ProjectId)
);
INSERT INTO Seminar VALUES(1, '2018-04-03', '10:00:00 AM', 'Hyderabad', 'Automatic Robots'), (2, '2018-04-04', '12:00:00 PM', 'Vijayawada', 'Data Analysing using Big Data'), (3, '2018-04-05', '11:00:00 PM', 'Vijayawada', 'Data Analysing using R');
etc...
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.