Assignment1_ERDSolution_NehaPatidar

convert to ER  with show relationships

CONVERT TO USE CASE DIAGRAM

Please help me answer the following questions

Convert the following ER Model to Relation   Model

Convert er diagram to Relational database

LECTURER_ID LECTURER_NAME DEPT ID DEPT_NAME SUBJECT_CO DE SUBJECT_ NAME TEACHING_HOUR_PER_SU ВJЕCT BIT5078 DATABASE 1053 KRISHNAN 01 IT BIT5018 PROGRAMMING 4 ВАC1011 ВАC1022 ACCOUNTINGI ACCOUNTING2 4 ACCOUNTING 1195 НАМID 02 ВВА1011 ВВA2022 MANAGEMENT MARKETING 3 1247 AMELIA 03 BUSINESS 1. Based on the table above, draw the dependency diagram and identify all dependencies (including all partial and transitive dependencies).

.Here it isstudent Relation Student_ID Student_Name Dept_Name 0000 AAAAA Computer Science 1111 BBBBB Computer Science 2222 CCCCC Business Administration 3333 DDDDD Education 4444 EEEEE Physics 5555 FFFFF History   Student_Courses Relation Student_ID Course_ID Section  Year Grade 0000 CS 2203 2 2020 A 0000 CS 1101 1 2019 A 1111 CS 1101 5 2019 B 1111 CS 1102 1 2019 B 1111 CS 2204 1 2020 A 1111 CS 2401 1 2020 A 2222 BUS 1101 3 2019 C 2222 BUS 2201 1 2020 B 2222 BUS 3302 1 2020 B 3333 EDUC 5010 1  2019 A 3333 EDUC 5210 1  2020 B 4444 PHY 1101 4  2019 A 4444 PHY 2202 1  2020 C 4444 PHY 3304 1  2020 A Considering Student and Student_Courses relations shown above, discuss when Union, Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join should be used and provide examples using the above relations. I AM GETTING USED TO MYSQL WORKBENCH. (Kindly take me through the path)?

PART 3 Relational Model EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary AAAA DEPARTMENT Dnamc Dnumber Mgr_san Mgr_start_datc AAA DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Essn Dependent_name Sex Bdate Relationship Drawn the ER diagram Super_ssn Dno

Create a relational database design for the following data model For the image

Film Actor Language Film SMALLINT VARCHARZ (255) VARCHAR2 (2 SMALLINT SMALLINT PF actor d SMALLINT PF fim id .im_id languaged SMALLINT CHAR (20) SMALLINT name description release year anguage_id original_language SMALLINT renta duration *rentaate engh *replacement_cost rating pecial features ustpdate SMALLINT NUMBER (42) SMALLINT Fam Category SMALLINT SMALLINT NUMBER (52) VARCHARZ 6) VARCHAR2 (255) TIMESTAMP PF Sm id KA category Actor actor id first name SMALLINT VARCHARZ (45) last rame VARCHAR (4) nationality CHAR (2) Category category SMALLINT VARCHAR2 (2) Country country d SMALLINT U short_code CHAR (2) VARCHARU (2) name P parent c SMALLINT name Using the film database you have, answer the following queries in your schema then write them down in this paper. Question One: Select the total rental_rate for films grouped by their categories. Order the result (a from largest to smallest. Select all actors who acted together with JOE in any film. Use inner query. (b Create a view, which…

Can we have identifying relation between two strong entities in this digram in the attached picture. ?

hand draw an entity-relationship diagram to visually represent a database design this an example, you can choose any subject

1. Consider the following schema: Player plyld plyName Hilal Sport spld 1 Record #plyld #evCode place spName Football 1 1. El 2 Tarek Sultan 2 Tennis 1 E6 4 Swimming 2 Cycling 3 3 Salim E2 4 4 2 E5 1 Event 3 ЕЗ 32 evCode evTitle #spId evPlayes 3 E8 8 E4 3 Mini Foot Fest Ibri Tour El 1 75 4 E7 E2 4 15 E3 Oman Open Arab Sea Race 2 32 E4 3 36 Muscat Cycling Dhofar Fest E5 4 48 Еб 1 115 Indoors Cup Suhar Open E7 3 32 B8 2 64 Note: primary keys are underlined, foreign keys have #. Answer the following questions : (after executing the SQL command, copy the command and sereen shot of the result in a word file. Do not forget to indicate the question number, save the file with your student number and send it by email) Q3. Write SQL queries to do the following: A. List names of all players. B. List event titles of events of sport 2. C. Display the number of plaxsst of event E6. D. List names of players who obtained first place in any event. E. List average number of Swimming events participants.

a database that tracks students and their majors. Students may have multiple majors and students need not have a major declared. A student major must link back to a valid academic program. Reconstruct the ERD for this database and paste it below.

Normalize the table based on the ER-Diagram developed. Try to normalize the ER-Diagram to 3NF. We recommend: LucidChart Draw.io

Create a relational database design for the following data model For the image

Map this ER diagram to a Relational Model and normalize it if needed.

Answer the query using SQL. For each department which has more than 5 female employees, retrieve the department name, the number of female employees in the department, and their average salary.

convert the crow foot notation diagram into a chen's notation

estion Completion Status: AIRPORT FLIGHT AIRPORTCODE FLIGHTNUM FK AIRPORT1 CUSTOMER AIRPOR NAME DTIME CUSTOMERNUM CITY DDATE FIRSTNAME ATIME ADATE DESTINATIONAIRPORT PRICE AIRPLANENUM(FK) AIRPORTCODE(FK) STATE MIDDLENAME LASTNAME 3. STREETNUMBER STREETNAME CITY FK AIRPORT I STATE PHONE 1. FK AIRPLANE FK_FLIGHT FK CUSTOMER AIRPLANE AIRPLANENUM PAYMENT FLIGHTNUM(FK AIRPLANENAME CUSTOMERNUM(FK) CAPACITY CREDITCARDTYPE AIRPORTCODE(FK) CREDITCARDNUM BOOKDATE HOLDDATE EXPIREDHOLDDATE A. 1 How many primary key in the Flight entity? B.5 How many tables in the system can Customer table retrieve data from? C. 4 How many attributes in the Airport Table? D.5 How many tables in the system? E. 5 How many relationships shown in the system? Click Save and Submit to save and submit. Click Save All Answers to save all ansuwers.

Q2

Based on the given ERD, identify all the business rules and explain them.

room_number student_name major Lives M Student in M student_ID enroll maintenance supervisor time оссирancy book class Course course_name Building M section_number M bldg number Sq. ft. teach 1 terminal degree institution room_number instructor_ID M office Instructor office_hours instructor _name FIGURE 8.6 ER diagram showing a STUDENT/COURSE/INSTRUCTOR/BUILDING database.

4. Write the business rules against the following E-R diagram. movie_cast act_id mov_id |integer integer char (30) actor genres Y act_id act_fname integer char (20) Y gen_id integer char (20) role gen_title act_Iname act_gender char (20) char (1) movie mov_id mov_title mov_year mov_time mov_lang mov_dt_rel mov_rel_country char (5) integer char (50) integer integer char (50) date director movie_genres Y dir_id integer integer char (20) char (20) mov_id dir_fname gen_id integer dir_Iname rating mov_id rev_id integer integer integer num_o_ratings integer reviewer movie_direction rev_id integer char (30) dir_id integer rev_stars mov_id integer rev_name w3resource.com 81 8 8Ị 8Ị 81 81

create one domain class diagram Use Case 1 Use Case Name: Self-service check in for road test Main Flow:   Driver System 1 Requests to check in for road test. Prompts for scanning driver’s license. 2 Scans driver’s license. Searches driver’s information using the scanned driver’s license number, displaying driver’s full name, license number, expiry date, type of license, and booked road test information (date, time and location of the road test). Prompts for confirmation. 3 Confirm to check in. (*1) Records the check in by changing the road test status to checked-in; adds the driver into the waiting list for road tests. Alternate Flows   Alternate Flow Description A1 Some information displayed is not correct. Driver will not confirm the check in and system will prompts the driver to go to a clerk for help.

Writhe the description for this EERD include the attributes and the entitty and the relationship like the hospital has many branches ……

1. Consider the following schema: Player plyId plyName Hilal Sport spld 1 Record #evCode place spName Football #plyId 1 Tarek Tennis 1 El 2 Sultan 1 Еб 4 Swimming Cycling 3 3 4 Salim 4 2 E2 1 2 E5 1 Event 3 ЕЗ 32 3 E8 8 evCode ev Title #spId evPlayes E4 3 Mini Foot Fest Ibri Tour Oman Open E1 1 75 4 E7 E2 15 ЕЗ 2 32 Arab Sea Race Muscat Cycling Dhofar Fest Indoors Cup Suhar Open E4 3 36 E5 4 48 Еб 1 115 E7 3 32 B8 64 Note: primary keys are underlined, foreign keys have #. Answer the following questions : (after executing the SQL command, copy the command and sereen shot of the result in a word file. Do not forget to indicate the question number, save the file with your student number and send it by email) Q6. Create a view named games that contains event title, number of event plaxes, sport name of all events.