Oracle Master-Detail Relationship Case Study

-

 Case Study 1

Master-Detail Relationship

1. Labwork for Master Table

Manda  Upazila under Naogaon district has 20 high schools and 20 madrasas. Upazila education officer wants to make a database comprising school master information.  He wants to develop a successful RDBMS.

 School table holds data type as follows:

 

 Master table name:  school

 

Fields

Data type and width

sc_code

number(4) primary key

sc_name

varchar2(40)

sc_location

varchar2(40)

sc_union

varchar2(40)

sc_upazila

varchar2(40)

 We can add sc_reg_date filed also.

Questions:

1.    Create a school  table that will be a master table for holding school information

2.    Insert 40 school information at the above table.

3.    Write some SQL for query data from the above table

4.    No duplicate school code data entry is allowed.

5.    School and Madrasa codes will be different.

6.    Delete some unwanted school records from the school table

7.    Update school name using school code.


/*


create table school (sc_code number(4) primary key, 

sc_name varchar2(30),sc_location varchar2(30), 

sc_union varchar2(30), sc_upazila varchar2(30));

*/


-- school entry


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(1000,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(1001,'ViQ Noon','Bely Road','Shanti nagar','Ramna');




insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(1002,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(1003,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(1004,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(1005,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(1006,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(1007,'ViQ Noon','Bely Road','Shanti nagar','Ramna');



-- Madrasa Entry


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(5000,'Alia Madrasa','Bakhsi Bazar','BUET','BUET');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(5001,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(5002,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(5003,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(5004,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(5005,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


insert into school (sc_code,sc_name,sc_location,sc_union,sc_upazila)

values

(5006,'ViQ Noon','Bely Road','Shanti nagar','Ramna');


2. Lab work for Master-Detail relationship

Manda Upazila has 20 high schools and 20 madrasas. Upazila education officer wants to create a database comprising student information.  He wants to develop a successful RDBMS.

 The student table holds data types as follows:

Detail table name:  student

 

Fields

Data type and width

sc_code

number(4) foreign key

st_roll

number(6) primary key

st_name

varchar2(30)

st_sex

char(1)

st_sub1

number(3)

st_sub2

number(3)

st_sub3

number(3)

st_sub4

number(3)

st_tot_marks

number(4)

st_result

char(2)





Questions:

1.    Create a  student table as the detail that will maintain a master-detail  relationship with a school table

2.    Insert school/madrasa wise student as table structure designed.

3.    write a query SQL command that will display sc_code, sc_name, sc_upazia, st_roll, st_name.


Following is the Oracle Master Detailed  case study Solution-

-- Creating Detail Table

DROP TABLE sch

DROP TABLE student;

-- Master Detail Relationship

                                                                -- Creating Master table

CREATE TABLE school

(sc_code number(4) PRIMARY KEY,

sc_reg_date date,

sc_name varchar2(30),

sc_location char(20),

sc_union varchar2(20),

sc_upazila varchar2(20));

 DESC school;

-- Inserting data in a school table

-- School code start by 1000

-- Madrasa code start by 5000

INSERT INTO school

VALUES (1000, TO_DATE('05 Aug, 2021', 'DD MM, YYYY'),

'Govt. Laboratory High School','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (1000, TO_DATE('05 Aug, 2021', 'DD MM, YYYY'), 'Govt. Laboratory High School','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (1001, TO_DATE('02 Feb, 2001', 'DD MM, YYYY'), 'Cantonment High School','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (1002, TO_DATE('20  Sep, 2010', 'DD MM, YYYY'), 'Cantonment High School','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (1003, TO_DATE('16 Oct, 1920', 'DD MM, YYYY'), 'Cantonment High School','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (1004, TO_DATE('25 Dec, 1985', 'DD MM, YYYY'), 'Cantonment High School','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (1005, TO_DATE('28 Jul, 1975', 'DD MM, YYYY'), 'Mirpur High School','Sabaihat','Tentulia','Manda');

 INSERT INTO school             VALUES (5000, TO_DATE('05 Aug, 2021', 'DD MM, YYYY'), 'Govt. Alia Madrasa','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (5001, TO_DATE('05 Aug, 2021', 'DD MM, YYYY'), 'Sarsina Madrasa','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (5002, TO_DATE('09 Nov, 1990', 'DD MM, YYYY'), 'Newmarket Fadil Madrasa','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (5003, TO_DATE('04 Jan, 2010', 'DD MM, YYYY'), 'Cantonment High School','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (5004, TO_DATE('04 Mar, 2007', 'DD MM, YYYY'), 'Cantonment High School','Sabaihat','Tentulia','Manda');

INSERT INTO school             VALUES (5005, TO_DATE('04 Mar, 2007', 'DD MM, YYYY'), 'Dhaka Alia Madrasa','Sabaihat','Tentulia','Manda');

-- Query

SELECT sc_code, sc_reg_date,sc_name,sc_upazila FROM school;

-- Creating  Detail Table

DROP TABLE student;

CREATE TABLE student (st_roll NUMBER (6),

sc_code NUMBER (4),

st_name VARCHAR2 (30),

st_sub1 NUMBER (3),

st_sub2 NUMBER (3),

st_sub3 NUMBER (3),

st_sub4 NUMBER (3),

st_tot_marks NUMBER (4),

st_result CHAR(2), PRIMARY KEY (st_roll),FOREIGN KEY (sc_code) REFERENCES school);

 DESC student;

INSERT INTO student (st_roll,sc_code,st_name,st_sub1,st_sub2,st_sub3,st_sub4)

VALUES (100000,1000,'Md. Shaowkat Hossain',90,85,96,90);

INSERT INTO student (st_roll,sc_code,st_name,st_sub1,st_sub2,st_sub3,st_sub4)

VALUES (100001,1000,'Md. Mobarok',70,85,92,60);

INSERT INTO student (st_roll,sc_code,st_name,st_sub1,st_sub2,st_sub3,st_sub4)

VALUES (100002,1000,'Md. Salam',50,85,96,99);

INSERT INTO student (st_roll,sc_code,st_name,st_sub1,st_sub2,st_sub3,st_sub4)

VALUES (100003,1000,'Rokeya Begum',98,96,98,99);

INSERT INTO student (st_roll,sc_code,st_name,st_sub1,st_sub2,st_sub3,st_sub4)

VALUES (100004,1000,'Chameli',100,100,100,100);

INSERT INTO student (st_roll,sc_code,st_name,st_sub1,st_sub2,st_sub3,st_sub4)

VALUES (100005,1000,'Israt',100,100,100,100);

INSERT INTO student (st_roll,sc_code,st_name,st_sub1,st_sub2,st_sub3,st_sub4)

VALUES (100006,1000,'Md. Dewan',38,32,30,25);

INSERT INTO student (st_roll,sc_code,st_name,st_sub1,st_sub2,st_sub3,st_sub4)

VALUES (500000,5000,'Md. MOHIBUR',77,85,90,52);

INSERT INTO student (st_roll,sc_code,st_name,st_sub1,st_sub2,st_sub3,st_sub4)

VALUES (500001,5005,'Md. MOHIBUR',77,85,90,52);


-- Quarry from both tables at a time


www.rashid-bd.info

 

 

 

 


Comments

  1. Mohammed Jowel RanaAugust 10, 2021 at 9:14 PM

    Dear Sir, It is a excellent outlook for knowing about Technology Innovation and Research.

    ReplyDelete
  2. Harrison keatMarch 17, 2022 at 11:05 PM

    Care home accidents can happen to anyone inside a care home, or in the grounds. This includes staff, residents and visitors carehome accident claims

    ReplyDelete

Post a Comment

Popular posts from this blog

Differences between the terms Science, Technology, and Engineering

-
What are the differences between the terms Science, Technology, and Engineering? Dr. Engr. Md. Abdur Rashid Director (Admin & Finance) NAPD Science Focus: The systematic study of the natural world through observation, experimentation, and analysis. Goal: To understand and explain the universe, its laws, and its phenomena. Methods: Scientific inquiry involves formulating hypotheses, conducting experiments, collecting data, and drawing conclusions. Examples: Physics, chemistry, biology, astronomy, geology. Technology Focus: The application of scientific knowledge to create tools, machines, and systems that solve problems or fulfill human needs. Goal: To improve efficiency, productivity, and quality of life. Methods: Technological development often involves innovation, design, and engineering. Examples: Computers, smartphones, automobiles, medical devices, renewable energy systems. Eng...
Read more

Developing Personal Skills

-
  Developing Personal Skills Engr. Md. Abdur Rashid  PhD, NAPD Dear Officers and Staff, you must establish and ensure the long-term growth of your organization. Maintain the following managerial abilities in order to enhance personal skills on a daily basis while working. 1.                  1)       Self-assessment 2)       Creativity 3)       Innovation 4)       Listening and feedback 5)       Find self-satisfaction 6)       Leadership 7)       Team building 8)       Hunt knowledge 9)       Self-reliance and judgment 10)    Resilience 11)    Time management 12)    Stress management 13)    Control over anything 14)    Find satisfa...
Read more

PhD Tips

-
  PhD Tips Dr. Engr. Md. Abdur Rashid Director (Admin & Finance), NAPD I’d like to share my personal experiences with the PhD journey from my perspective- 1. A PhD doesn't demand extraordinary intelligence, but rather an immense amount of patience and perseverance. 2. The scope of your research will not be vast, but rather a very specific and narrow focus. 3. Your supervisor cannot do everything for you; they serve as a guiding umbrella. Ultimately, you will be your own supervisor. 4. Keeping a PhD diary is essential. Write down every thought, idea, or insight as soon as it comes to you—even after leaving the washroom. Make sure to follow through on each task and mark it off once completed. 5. From day one to the very end, adhere strictly to the guidelines provided by your university and supervisor. Review other PhD reports to enhance the quality of your work. 6. Avoid being overly ambitious about publishing in renowned journals. It takes time. Stick to your university...
Read more