Question

Please answer this questions as required

I couldn't delete the old pic so the new one is butter and for the same question.

RealGree uses the paper form as below to record the weekly (just one week in this 5. A lawn and tree care company ion) informati application to keep track of the information of the part-time employees. Please (a) develop the E-R diagram (8 points). ion about is part-time employees (one form for one employee). The company asks you to create a o the table structures. Each table should include table name, attributes, and the primary key (6 points). Employee ID: Employee Name: Employee Pay Rate: Employee Weekly Working Hours: Department ID: Department Name: (c). write the SQL statements to answer (16 points) cl. List name of employees in "Tree" Department (only department name is known) (use a join). c2. List name of employees in Tree" Department (only department name is known) (use a subquery) c3. Calculate the weekly pay (PayRate Hours) for each employee. c4. Calculate the average pay rate grouped by department name. 4. Develop a database to support the tracking needs of a university. The university wants to know: the names of student clubs does a student join (suppose a student can join several student clubs, a club has many students)? Which professor is the advisor of a club? (suppose a professor can advise several clubs, and a club can only have one professor as the advisor) What courses does a professor teach? (suppose a professor can teach several courses, and a course can be taught by several professors). (1). Develop the E-R Diagram (10 points). (2). Map the ERD to table structures, each entity table needs an ID field as the primary key and a Name field (8 Points). (3). Write the SQL statements to answer: (12 points) a. List clubs' names that the student "Tom" (only student name is known) belongs to. b. List Club "Badminton's (only club name is known) advisor c. List the course names which Professor "Smith" (only professor name is known) teaches. a database to support the tracking needs of a university. The university wants to know: the names of student 4. Develop a a student join (suppose a student can join several student clabs, a club has many students)? Which professor is clubs does of a club? (suppose a professar can advise several clubs, and a club can cnly have one professor as the advisor). t courses does a professor teach? (suppose a professor can teach several courses, and a course can be taught by several professors). (1). Develop the E-R Diagram (10 points). (2). Map the ERD to table structures, esch entity table needs an ID field as the primary key and a Name field (8 Points). (3). Write the SOL statements to answer: (12 points) a. List clubs' names that the student "Tom (only student name is known) belongs to. b. List Club "Badminton"'s (only club name is known) advisor c. List the course names which Professor "Smith (only professor namme is known) teaches. s A lawn and tree care company RealGreen uses the paper form as below to record the weekly (just one week in this question') information about its part-time employees (one form for one employee) The companyssou to create a dstabase application to keep track of the information of the part-time employees. Please (a) develop the E-R diagram (8 points) (b) develop the table structures. Each table should include table name, attnbutes, and the primary key (6 points) Employee ID Employee Name Employee Pay Rate Employee Weekly Working Hours Department LD Department Name: (c). write the SQL statements to answer (16 poinms) cl. List name of employees in Tree" Department (only department name is kaown) (use a join). c2. List name of employees in "Tree" Department (only department name is known) (use a subquery) c3. Caleulate the weekly pay (PayRate Hours) for each employee c4, Calculate the average pay rate grouped by department name.

Answer 1

5) a) e-R dra gaam Depot mm lon 3r to employee Nome Emplbyee Poy Ra e This is n: retahonship because an emmpiDyee belons to onty one depotment Gut a depostmen con have more than oneemployee These will o totol porhei paton om Eml coute nploees belonstoS enhty eves Some Deporimen But tmgse con be a depantment nich has no leey attaibute fos Empoy ee Enhty is tcey attsibute fos Deportment Enit y ermplbyees Employee I D iS Depoxtment I 6 Toble shuctunes Toble Name Employee AttoibutesID NTEGER Em ptoyee Name CHe(uo) mplbye y Rate INTEMER Emptoyee weelcly wolenghas INTE GE Department ID TNTEGER bergncey to bimaay ley : EmployeeID Depoitment Table

Toble Nome: Depostment ttilautes : Deportment D CINEMER) Depotl mert Nowe CHAR(40) Paimany k De parlment ID (c) .Select Employee-Name (tom Employee TNNERTolN Depn tment ON Employee. tspitoyeesst Deea tment WHERE Depan tment. Depontmen t . Depot ment-zd G) Select Employee-Name bom Emplogee wheve 10 Select Depantment Depot ment. DepantmentID fbom Depatment wheve Depariment De pastmont Name Ca) Select Employee 1D, Employer Nome, Emplbyee Poy Rate x Employee weetctywos inghs as eecty- Pay E mployee

) Select AnEmoyre Pay Rate) oon Employee INNER JOTN Depontment ON Emplose. DepostmentiD oid Student Name Nime goche ome OU1Se ㄚㄧㄧ Cseid Tabes: Toble Nome: Student Ptibutes StudentID CINTEnER Poimas cey NimE CHAR (uD student ID c 그Ntegex)っfooiegn rry to Student . Attsibutes : Icey INTEGER lubNom e CHARro ClubArtsibu tes: lbID

Fosetgh key to pinpetor.ro, rounei o) (b) Select propessos nome som ula NNEP o N tuhese lub ctubniome Bointon Teaches Ca) Seleck lvb wane fiom club TNNEP Tos N club, clubid-Belo ngs_то , cruUTD Belo ngSmro ON twhese Gelong To StudentIo IN C Select Student I0 om Student whefe student Student veme om)