الرجاء مساعدتي في رسم Erd و Sql Statements عاجل وضروري

[undercover]

 

هذا السؤال :

Scenario:

Consider the following scenario modeling project management activities in an organization. PDA Oman conducts many star gazing shows to the children of different age groups. Each show has a unique code, name, time of the day and language. Once a child visits the center, the Customer Relation Officer will registers the details of the child details such as name, Parents contact number, age, school name, civil ID number, which is unique to every child. A child can choose any number of shows or he may not choose any show, a show should be chosen by at least one child to be conducted. Whenever a child chooses a show, the date will be recorded to calculate the credits of the child. Each show is facilitated by a coordinator; The Coordinator is identified by his ID, name, and phone number. A show should have at least one coordinator and the coordinator can facilitate one or more shows. When a coordinator facilitates a show, PDO records the date and no. of hours for calculating incentive for the coordinator.

 

Task1 : Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, attributes of each entity including primary key, not null, relationship between the entities and cardinality constraints. State any assumptions

 

necessary to support your design.

 

task 2: write the SQL statements to create all the relations/tables identified in task 1. Include different table constraints (e.g. primary key, foreign key, not null, integrity constraints, etc.)

Write SQL statement to Insert at least 5 records for each table created above- .

. Write at least two SQL statements having “subqueries” based on the tables created above to demonstrate the concept of “subquery”.

. Write at least two SQL statements having “joins” based on the tables created above to demonstrate the concept of “Join”.

 

 

 

[undercover]

حاولت حل السؤال لكن يوجد اخطاء حسب ما اعتقد

qq.pdf

[undercover]

for task 2 :

 ((create table shows (sh_code varchar(25) primary key ,sh_name varchar(30) not null , sh_time not null , sh_language varchar(20
select* from shows
insert into shows values (111, 'stars',9:00 ,'English'
insert into shows values (112, 'tonight', 10:30 ,'English
insert into shows values (113, 'the sweet', 9:30 ,'Arabic
insert into shows values (114, dream', 11:00 ,'English
insert into shows values (115, 'the child', 10: 30 ,Arabic'




 ((create table child (ch_civil_id varchar(25) primary key ,ch_name varchar(30) not null , ch_parents_contact_nu not null,  ch_age not null , ch_sclool varchar(20
select* from shows
insert into child values (11122, 'Ali',99998876 , 9, 'Abn AlQasim school'
insert into child values (11223, 'Ahmad', 91188989 , 8, 'Abn AlQasim school
insert into child values (11324, 'Sara',99454621 , 10, 'Alhuda school
insert into child values (11425, Noor', 92678453 , 7, 'AL Baian school
insert into child values (11526, 'Reem', 99773344 , 8, Alhuda school




 (create table coordinator (coo_id varchar(25) primary key ,coo_name varchar(30) not null , coo_PN not null 
select* from shows
insert into coordinator values (0001, 'Hand',99543532'
insert into coordinator values (0002, 'Reham' ,'98643211
insert into coordinator values (0003, 'zahra',97541356
insert into coordinator values (0004, 'Huda', 87542334
insert into coordinator values (0005, 'Mohammed', 97654322'


SELECT coo_name
      FROM coordinator
      WHERE coo_name <comparison   operator> (SELECT statement

[Amgad]

السلام عليكم ورحمة الله 

 

في رسمة ال erd  التى ارفقتها في موضوعك توجد بعض الملاحظات 

 

- جدول ال child 

-- مفتاح الجدول هو civil_ID_number فقط

-- child_name لا يعتبر pk

-- Parent_contact_nu  لا يعتبر pk 

 

يوجد جدول مهم وهو غير موجود بالرسم ... وهو الذي يربط بين الثلاثة جداول معا 

وهو حضور الchild  ال show 

بافتراض ان هناك عملية تسجيل لكل حضور لل child لمشاهدة show  معين وتحت اشراف Coordinator معين 

سيتم انشاء الجدول الوسيط show_child

كما في الرسم التالي 

يوجد مخططين للرسم pdm ,

 ERD=cdm 

 

 

/*==============================================================*/
/* Table: COORDINATOR                                         */
/*==============================================================*/
CREATE TABLE COORDINATOR 
(
   COORDINATOR_ID     NUMBER               NOT NULL,
   COORDINATOR_NAME   VARCHAR2(60),
   PHONE_NUMBER       NUMBER,
   CONSTRAINT PK_COORDINATOR PRIMARY KEY (COORDINATOR_ID)
);


/*==============================================================*/
/* Table: CHILD                                               */
/*==============================================================*/
CREATE TABLE CHILD 
(
   CIVIL_ID_NUMBER    NUMBER               NOT NULL,
   CHILD_NAME         VARCHAR2(60),
   CHILD_BIRTHDATE    DATE,
   PARENT_CONTACT_NU  NUMBER,
   SCHOOL_NAME        VARCHAR2(100),
   CONSTRAINT PK_CHILD PRIMARY KEY (CIVIL_ID_NUMBER)
);


/*==============================================================*/
/* Table: SHOW                                                */
/*==============================================================*/
CREATE TABLE SHOW 
(
   SHOW_CODE          NUMBER               NOT NULL,
   SHOW_NAME          VARCHAR2(100),
   SHOW_TIME          DATE,
   SHOW_LANGUAGE      VARCHAR2(40),
   CONSTRAINT PK_SHOW PRIMARY KEY (SHOW_CODE)
);


/*==============================================================*/
/* Table: SHOW_CHILD                                          */
/*==============================================================*/
CREATE TABLE SHOW_CHILD 
(
   CHOOSE_SERIAL      NUMBER               NOT NULL,
   CHOOSE_DATE        DATE,
   CIVIL_ID_NUMBER    NUMBER               NOT NULL,
   SHOW_CODE          NUMBER               NOT NULL,
   COORDINATOR_ID     NUMBER               NOT NULL,
   CONSTRAINT PK_SHOW_CHILD PRIMARY KEY (CHOOSE_SERIAL),
   CONSTRAINT FK_SHOW_CHI_REFERENCE_CHILD FOREIGN KEY (CIVIL_ID_NUMBER)
         REFERENCES CHILD (CIVIL_ID_NUMBER),
   CONSTRAINT FK_SHOW_CHI_REFERENCE_SHOW FOREIGN KEY (SHOW_CODE)
         REFERENCES SHOW (SHOW_CODE),
   CONSTRAINT FK_SHOW_CHI_REFERENCE_COORDINA FOREIGN KEY (COORDINATOR_ID)
         REFERENCES COORDINATOR (COORDINATOR_ID)
);

[undercover]

وعليكم السلام ورحمة الله 

جزاك الله خيرا .. 

عندي استفسار بشان  NUMBER هل يقصد به not null?????

 

وهل اجابة task 2 صحيحه؟؟

بخصوص SQL statements having “subqueries”  the concept of “subquery”.

. SQL statements having “joins”

[Amgad]

السلام عليكم ورحمة الله 

 

ال NOT NULL  هي من خصائص  ... العمود أو الحقل بالجدول 

 

مثل COORDINATOR_ID  

يجب أن يحتوي قيمة ... عند انشاء السجل في الجدول 

وهذا بسبب ان هذا الحقل هو المفتاح الأساسي بالجدول pk  ويجب أن يحتوي قيمة 

 

الاوامر المطلوبه مع ال SQL

subquery

joins

 

مرتبطه بتسجيل بيانات في الجدول SHOW_CHILD

 

سجل بيانات في الجدول الأخير ... ثم يمكنك إعداد اوامر ال SQL  المطلوبه 

[undercover]

السلام عليكم..

هذا مرفق به تعديل للحل 

وهل كودات SQL ل sub-quire و join صحيحه؟

RE.pdf

[undercover]

المعذره الان انتبهت بان البيانات ل SQL مرتبطه بجدول show_child