Exercise Q1: Regarding the following scenario and the main concepts read the presented requirements and answer the questions: Entities of a mini world relationships can have attributes, operations, dependencies, inherits relations, and aggregations. A set of rules is associated with each of these constructs. Entities are represented by means of their properties, called attributes. All attributes have values. For example, a student entity may have name, class, and age as attributes. There exists a domain or range of values that can be assigned to attributes. In terms of DBMS, an entity is a table or attribute of a table in database, so by showing relationship among tables and their attributes, ER diagram shows the complete logical structure of a database. While, the database schema represents the whole relations exists in a given database. In a part of a university mini world environment, we created several entities to identify the main relations such as COURSE, CLASS, STUDENT, INSTUCTOR and DEPARTMENT. - THE STUDENT'S STUDIES BY INSTRUCTORS, WHILE EACH INSTUCTOR WORKS IN A DEPARTMENT. EACH DEPARTMENT HAS NAME, A UNIQUE NUMBER. - THERE ARE NUMBER OF INSTUCTORS TEACH THE STUDENTS.EACH STUDENT HAS NAME, ID, CLASS, GROUP, PHONE_NUM (STUDENT MAY HAVE MORE THAN ONE NUMBER). - INSTUCTORS TEACH COURSES. EACH COURSE HAS NUM_CO, NAM_CO, TIME, LEVEL AND TEXTBOX. - INSTRUCTORS ATTEND CLASSES. EACH CLASS HAS DPT_NO, S_NO, A BUILDING_NO. - EACH INSTUCTOR HAS NAME, SI_NUM, OFFICE_NUM, DPT_NO. Department have many numbers of instructors and each instructor can work in only one department. Instructor can teach many students and students can be taught by number of instructors. students learned many courses and many courses can be taught to students. A- Draw an ER-Diagram for the University mini world environment, specifying the following: (Entities, Attributes, Primary Key) for each entity and the relationships between entities. B- Map your ER-Diagram in the form: R(A1,A2,A3,.,AN) specifying the primary key and the foreign key. that keeps track of student enrollment in courses and the books adopted for each course: - STUDENT (SSN, Name, Major, Bdate) - COURSE (Course, Cname, Dept) - ENROLL (SSN, Course, Quarter, Grade) - BOOK_Adoption (Course, Quarter, Book_ISBN) - TEXT (Book ISBN, Book_Title, Publisher, Author) Draw a relational schema diagram specifying the foreign keys for this schema. Q3: In the form of SQL commands of database language write the corresponding command of the following statements applied on the presented tables. \begin{tabular}{|l|l|l|l|l|l|} \hline Std_name & Std id & Major & Bdate & Group & Phone_num \\ \hline \end{tabular} Course table \begin{tabular}{|l|l|l|l} \hline Num co & Nam_co & Time & level \\ \hline \end{tabular} - Add the constraint check in (student) table to examine the values of group column to be ranged from 1 to 4 . - Add 4-rows of new students enrollment in (student) table. - Update the level of the course number (112) to be 2 instead of 4 in (course) table. - Update the time of the course number (112) to 12:00 instead of 9:00 in (course) table. - Modify the content of data by updating the Major of the student (23452) to be Web instead of Po. - In the (student) table, drop column Bdate and Major
