3. Given the database design schema below a.Design the database in MS Access wit
ID: 3705609 • Letter: 3
Question
3. Given the database design schema below
a.Design the database in MS Access with needed tables and attributes in each table.
b.What type of data warehouse design schema is used?
c.Which table is the fact table?
d. Populate the tables by running the following SQL statements to load data into your database.
/***** TIMELINE ******************************************************/
INSERTINTOTIMELINE VALUES(
41034,'05-May-12', 5,'May',2,'Qtr2',2012);
INSERTINTOTIMELINE VALUES(
41037,'08-May-12', 5,'May',2,'Qtr2',2012);
INSERTINTOTIMELINE VALUES(
41039,'10-May-12', 5,'May',2,'Qtr2',2012);
INSERTINTOTIMELINE VALUES(
41041,'12-May-12', 5,'May',2,'Qtr2',2012);
INSERTINTOTIMELINE VALUES(
41075,'15-Jun-12', 6,'Jun',2,'Qtr2',2012);
INSERTINTOTIMELINE VALUES(
41079,'19-Jun-12', 6,'Jun',2,'Qtr2',2012);
INSERTINTOTIMELINE VALUES(
41095,'05-Jul-12', 7,'Jul',3,'Qtr3',2012);
INSERTINTOTIMELINE VALUES(
41098,'08-Jul-12', 7,'Jul',3,'Qtr3',2012);
INSERTINTOTIMELINE VALUES(
41100,'10-Jul-12', 7,'Jul',3,'Qtr3',2012);
/***** EMPLOYEE ******************************************************/
INSERTINTOEMPLOYEE VALUES(
1,'Smith','Sam','Master');
INSERTINTOEMPLOYEE VALUES(
2,'Evanston','John','Senior');
INSERTINTOEMPLOYEE VALUES(
3,'Murray','Dale','Junior');
INSERTINTOEMPLOYEE VALUES(
4,'Murphy','Jerry','Master');
INSERTINTOEMPLOYEE VALUES(
5,'Fontaine','Joan','Senior');
/***** PROPERTY *******************************************************/
INSERTINTO[PROPERTY] VALUES(
1,'Eastlake Building','Seattle','WA','98119');
INSERTINTO[PROPERTY] VALUES(
2,'Elm St Apts','Lynwood','WA','98223');
INSERTINTO[PROPERTY] VALUES(
3,'Jefferson Hill','Bellevue','WA','98007');
INSERTINTO[PROPERTY] VALUES(
4,'Lake View Apts','Redmond','WA','98052');
INSERTINTO[PROPERTY] VALUES(
5,'Kodak Heights Apts','Rochester','NY','14604');
INSERTINTO[PROPERTY] VALUES(
6,'Private Residence', 'Bellevue','WA','98007');
INSERTINTO[PROPERTY] VALUES(
7,'Private Residence','Bellevue','WA','98007');
INSERTINTO[PROPERTY] VALUES(
8,'Private Residence','Rochester','NY','14604');
/***** SERVICE **********************************************************/
INSERTINTOSERVICE_HOURS VALUES(41034,1,1,4.50);
INSERTINTOSERVICE_HOURS VALUES(41037,3,3,4.50);
INSERTINTOSERVICE_HOURS VALUES(41039,2,2,2.75);
INSERTINTOSERVICE_HOURS VALUES(41041,5,4,7.50);
INSERTINTOSERVICE_HOURS VALUES(41044,8,4,2.75);
INSERTINTOSERVICE_HOURS VALUES(41048,4,1,3.00);
INSERTINTOSERVICE_HOURS VALUES(41048,7,2,2.50);
INSERTINTOSERVICE_HOURS VALUES(41065,1,1,4.50);
INSERTINTOSERVICE_HOURS VALUES(41068,3,3,4.50);
INSERTINTOSERVICE_HOURS VALUES(41070,6,5,2.50);
INSERTINTOSERVICE_HOURS VALUES(41072,5,4,7.50);
INSERTINTOSERVICE_HOURS VALUES(41075,8,4,2.75);
INSERTINTOSERVICE_HOURS VALUES(41079,4,1,3.00);
INSERTINTOSERVICE_HOURS VALUES(41079,7,2,2.50);
INSERTINTOSERVICE_HOURS VALUES(41095,1,1,4.50);
PROPERTY 0PropertyID City State Zip SERVICE_HOURS TimelD ? 7 Property!D ? ? EmployeeID TIMELINE TimelD EMPLOYEE MonthID MonthText EmployeeID LastName FirstName YearExplanation / Answer
b.What type of data warehouse design schema is used?
Answer b)
Here the data warehouse design schema used is Star Schema. Here the dimensions are employee, properties and timeline. In star schema the fact table is referenced by the dimension tables. This is required to fetch the output of the query for business from the fact table. Mainly fact is used for measurement.
c.Which table is the fact table?
Answer c)
Here the fact is service_hours, where Hours_worked is measured from the employee,properties and timeline dimension.
If you require or have any doubt please mention in comment.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.