In this module, we have learned about the methods of security control testing. The steps in creating a secure

2 BYGB/ISGB 7973: ASSIGNMENT 2 DATABASE MANAGEMENT SQL Assignment Part I You

2

BYGB/ISGB 7973: ASSIGNMENT 2

DATABASE MANAGEMENT

SQL Assignment

Part I

You may want to use the CreateTable_TG.ddl that we used for the TradeGroup an exemplar for this question.

This exercise is based on the database described in figure 5-11 on page 244 of the textbook.

Write a ddl script to create relations and constraints for the database shown in Figure 5-11 of the textbook, (shorten, abbreviate, or change any data names, as needed for your SQL version).

The script should include:

Commands to create tables in Figure 5-11. The datatype for each attribute is listed below.

Commands to add the primary key constraint for each table as indicated by underlined attribute(s) in figure 5-11.

Command to add foreign key constraint for table SECTION to enforce referential integrity: before any row can be entered into the SECTION table, the CourseID to be entered must already exist in the COURSE table.

Commands to add foreign key constraints for table REGISTRATION to enforce referential integrity: before any row can be entered into the REGISTRATION table, the SectionNo to be entered must already exist in SECTION table and StudentID to be entered must already exist in STUDENT table.

Assume the following attribute data types:

StudentID (integer)

StudentName (25 characters)

FacultyID (integer)

FacultyName (25 characters)

CourseID (8 characters)

CourseName (15 characters)

DateQualified (date)

SectionNo (integer)

Semester (7 characters)

Part II

Problems and Exercises 5-46 through 5-56 in the text book are based on the relations shown in Figure 5-12 on page 246. The database tracks an adult literacy program. Tutors complete a certification class offered by the agency. Students complete an assessment interview that results in a report for the tutor and a recorded Read score. Each student belongs to a student group. When matched with a student, a tutor meets with the student for one to four hours per week. Some students work with the same tutor for years, some for less than a month. Other students change tutors if their learning style does not match the tutor’s tutoring style. Many tutors are retired and are available to tutor only part of the year. Tutor status is recorded as Active, Temp Stop, or Dropped

Using the connection for user SYS with role SYSDBA create a new user (schema) with user name as your last name.

Use the script provided

Remember if you are using Oracle on the Apporto virtual machine you will use the following

Server: nv-fdh-db3

Port: XXXX assigned to you by Apporto

SID: XE

Hint: Modify and run the script given on page 11 of Oracle XE & SQL Developer on Apporto – 2022

If you are using Oracle on your laptop you will use the following

Server: localhost

Port: 1521 assigned to you by Apporto

Service: XEPDB1

Hint: Modify and run the script given on page 13 of Installing Oracle 21c XE & Developer on Windows 10.

Create a connection as the new user.

Copy the script at the end of this document to create and populate tables corresponding to Figure 5-12 on page 247 of the text book. Note that Group is a reserved word in SQL. Therefore, I have changed the name of the second column in table STUDENT to Grp

Paste the script in the worksheet for the connection for the user you have created.

In addition to creating and populating tables, the script has incorporated uniqueness constrains and referential integrity constrains:

There can be only one row in Table TUTOR for a given value of TutorID

There can be only one row in table STUDENT for a given value of StudentID

TutorID in table MATCH_HISTORY must refer to an existing TutorID in table TUTOR

StudentID in table MATCH_HISTORY must refer to an existing StudentID in table STUDENT

Run the script. Check if all the steps in the script were executed successfully.

Work through questions 5-46 through 5-56 in the textbook (page 246). Submit your answer as a MSWord document that contains SQL query and the resulting table for each question.

Assume that the date when the queries are being built is August 1, 2018. For questions 5-53 and 5-56 which require date arithmetic, use NLV() function in the query to replace NULL value under column ENDDATE in table by 08/01/2018.  Do not update the data in the table.

Delete the record from table TUTOR with TutorID=104. Examine data in table MATCH_HISTORY. You will find that all records for TutorID =104 have also been dropped. We have therefore lost the history about tutoring students with Student ID 6 and 7. Why is that the case?

============

Use of NVL( ) to deal with null value

The following query will give a table that has all columns in table MATCH_HISTORY and an additional column that has either ENDDATE or null value replace by the 08/01/2018

SELECT MATCHID,TUTORID, STUDENTID, STARTDATE, ENDDATE, NVL(ENDDATE, ’08/01/2018′) as CURRENT_OR_ENDDATE
FROM MATCH_HISTORY;

(If you want to you can save the above query as a view using the command CREATE VIEW and then use it just like any other table)

MATCHID

TUTORID

STUDENTID

STARTDATE

ENDDATE

CURRENT_OR_ENDDATE

1

100

3000

1/10/2018

8/01/2018

2

101

3001

1/15/2018

5/15/2018

5/15/2018

3

102

3002

2/10/2018

3/1/2018

3/1/2018

4

106

3003

5/28/2018

8/01/2018

5

103

3004

6/1/2018

6/15/2018

6/15/2018

6

104

3005

6/1/2018

6/28/2018

6/28/2018

7

104

3006

6/1/2018

8/01/2018

Expressions involving dates

Oracle has many date-related functions.  You have already seen TO_DATE that converting a text string to internal date representation. Similarly in session 3, we saw TO_CHAR ( ) function that converts date into a character string.

In Oracle SQL the number of days between two dates is given by the simple arithmetic difference: Date2 – Date1, (positive if Date2 is after  Date1

 The difference in dates does not include starting date, so if an event starts and ends on the same date, the number of days will evaluate to 0. Similarly,  the query

SELECT (to_date(’09/30/2018′) – to_date(’09/01/2018′)) As DURATION
FROM DUAL;

will lead to the result

DURATION

29

 (TableName Dual stands for a dummy table in Oracle that we can use when we do not have an actual table or a view available to draw data from)

So to calculate the duration for the question 5-53 we can create a query on table MATCH_HISTORY that has a column

(NVL(ENDDATE, ’08/01/2018′) – STARTDATE) as DURATION

Script to create tables, constrains and insert data for figure 5.12

——————————————————–

— DDL for Table STUDENT

——————————————————–

CREATE TABLE STUDENT

( StudentID CHAR(5 BYTE) NOT NULL,

Grp CHAR(2 BYTE),

Read CHAR(5 BYTE)

);

——————————————————–

— DDL for Table TUTOR

——————————————————–

CREATE TABLE TUTOR

( TutorID CHAR(5 BYTE),

CertDate DATE,

Status CHAR(10 BYTE)

);

——————————————————–

— DDL for Table MATCH_HISTORY

——————————————————–

CREATE TABLE MATCH_HISTORY

( MatchID CHAR(2 BYTE) NOT NULL,

TutorID CHAR(5 BYTE),

StudentID CHAR(5 BYTE),

StartDate DATE,

EndDate DATE

);

——————————————————–

— DDL for Creating Unique Indexes for each table

——————————————————–

ALTER TABLE TUTOR ADD CONSTRAINT TUTOR_PK PRIMARY KEY (TutorID);

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK PRIMARY KEY (StudentID);

ALTER TABLE MATCH_HISTORY ADD CONSTRAINT MATCH_HISTORY_PK PRIMARY KEY (MatchID);

——————————————————–

— Ref Constraints for Table MATCH_HISTORY

——————————————————–

ALTER TABLE MATCH_HISTORY ADD CONSTRAINT MATCH_HISTORY_FK1 FOREIGN KEY (TutorID) REFERENCES TUTOR (TutorID) ON DELETE CASCADE;

ALTER TABLE MATCH_HISTORY ADD CONSTRAINT MATCH_HISTORY_FK2 FOREIGN KEY (StudentID) REFERENCES STUDENT (StudentID) ON DELETE CASCADE;

——————————————————–

— DML for data entry using Insert command

——————————————————–

Insert into STUDENT (StudentID,Grp,Read) values (‘3000’, ‘3’,’2.3′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3001’, ‘2’,’5.6′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3002’, ‘3’,’1.3′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3003’, ‘1’,’3.3′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3004’, ‘2’,’2.7′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3005’, ‘4’,’4.8′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3006’, ‘3’,’7.8′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3007’, ‘4’,’1.5′);

——————————————————–

Insert into TUTOR (TutorID,CertDate,Status) values (‘100′,to_date(’05-JAN-18′,’DD-MON-RR’),’Active’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘101′,to_date(’05-JAN-18′,’DD-MON-RR’),’Temp Stop’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘102′,to_date(’05-JAN-18′,’DD-MON-RR’),’Dropped’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘103′,to_date(’22-MAY-18′,’DD-MON-RR’),’Active’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘104′,to_date(’22-MAY-18′,’DD-MON-RR’),’Active’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘105′,to_date(’22-MAY-18′,’DD-MON-RR’),’Temp Stop’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘106′,to_date(’22-MAY-18′,’DD-MON-RR’),’Active’);

——————————————————–

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘1′,’100′,’3000′,to_date(’10-JAN-18′,’DD-MON-RR’),null);

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘2′,’101′,’3001′,to_date(’15-JAN-18′,’DD-MON-RR’),to_date(’15-MAY-18′,’DD-MON-RR’));

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘3′,’102′,’3002′,to_date(’10-FEB-18′,’DD-MON-RR’),to_date(’01-MAR-18′,’DD-MON-RR’));

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘4′,’106′,’3003′,to_date(’28-MAY-18′,’DD-MON-RR’),null);

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘5′,’103′,’3004′,to_date(’01-JUN-18′,’DD-MON-RR’),to_date(’15-JUN-18′,’DD-MON-RR’));

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘6′,’104′,’3005′,to_date(’01-JUN-18′,’DD-MON-RR’),to_date(’28-JUN-18′,’DD-MON-RR’));

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘7′,’104′,’3006′,to_date(’01-JUN-18′,’DD-MON-RR’),null);

——————————————————–

Commit;

Trifles Title Implications: Explain what the word “trifles” means. Do you think

In this module, we have learned about the methods of security control testing. The steps in creating a secure Writing Assignment Help Trifles

Title Implications: Explain what the word “trifles” means. Do you think it is a fitting title?

Symbolic Setting.

Where is the murder site/ farmhouse located?

B. Inside the house:

List two things that let the audience “feel” how literally cold it is:

List three significant details that are evidence of a poorly kept kitchen:

4. Thumbnail Sketch: Make a comment about each of the characters (one sentence)

A. Attorney Henderson_________________________________________________

B. Sheriff Peters ______________________________________________________

C. Mr. Hale __________________________________________________________

D. Mrs. Peters ________________________________________________________

E. Mrs. Hale _________________________________________________________

5. List two “put down” comments the men make against women:

6. Why does Mrs. Hale get upset with the men talking about Mrs.Wright?

7. Why do you think the women hide the dead bird?

Observation, Lab 1 Assignment Complete this assignment and attach your document as

Observation, Lab 1 Assignment

Complete this assignment and attach your document as a word file to the correct dropbox on Elearning before the due date. Review the rubric to understand how the assignment will be graded.

Learning to compare and contrast correctly is a great observational tool. Visit the compare and contrast webpage under the Assignments Helps and Instructions module to learn how to do this process correctly. Failure to do so will likely result in a lower grade.

List at least 3 comparisons (similarities) here.

1. Unlike animal cells, which lack a cell wall, plant cells contain cell walls.

2. Animal cells lack peroxisomes, whereas plant cells do, and plant cells lack green chloroplasts.

3. Plants have plasmodesmata but animals lack plasmodesmata, and animal cells lack lysosomes.

2.

3.

List at least 3 comparisons (similarities) here.

1. Unlike animal cells, which lack a cell wall, plant cells contain cell walls.

2. Animal cells lack peroxisomes, whereas plant cells do, and plant cells lack green chloroplasts.

3. Plants have plasmodesmata but animals lack plasmodesmata, and animal cells lack lysosomes.

2.

3.Typical visual features of an animal and plant cell can be found in Chapter 5 figure 5.17 (section 5.3) of your online text. Observe the pictures and answer the following questions. What similarities do you see? What differences do you see?

List at least 3 contrasts (differences) here.

1. Mitochondria are found in both plant and animal cells.

2. Animal and plant cells both have a nucleus.

3. The Golgi apparatus is found in both plant and animal cells.

List at least 3 contrasts (differences) here.

1. Mitochondria are found in both plant and animal cells.

2. Animal and plant cells both have a nucleus.

3. The Golgi apparatus is found in both plant and animal cells.

Now reflect on what you just observed and learned in looking at the two cell types. Take a step back and think about the bigger ideas as opposed to the details you listed above. What have you learned by looking at these two different types of cells? In a short essay, explain what you have learned. 100 word minimum (words will be counted).

By observing the two cells and their variability, I discovered that various species have different needs for survival, which is why multicellular organisms each have something that is not observable in the other. For instance, plants have chloroplast, which itself is responsible for their green color and is the source of photosynthesis to make food for survival since they are immobile, but in animals this feature is absent. However, despite their differences, we can see many similarities between the two types of cells, such as the need for energy-producing mitochondria. As a result, I also learned that these similarities serve as the basis for classification, with both plant and animal cells belonging to the phylum Eukarya. Finally, I discovered that the secret to knowing how life functions is observation.

The plant cell you observed in figure 5.17 is an artist’s drawing of many different plant cells. It is likely that if you were to look at a plant cell under a microscope, you would never actually find one that looks like the figure. It is a synthesis of many different images. Search for “high magnification plant cell images” on the web. View a few of them. In a short essay, explain what you have learned. Since you are using someone else’s information, this will need to be referenced and cited correctly. 100 word minimum.

From the images I saw of plant cells, I realized that observation cannot be made from a single instance and that it is a process that requires repetition, as is the case when observing plant cells or any type of cell. One observation is never sufficient because there are occasionally details that we certainly see but are unaware of. As I studied several photos of plant cells, I discovered that a few of them were illegible or lacking elements that I discovered in other images. As a result, I concluded that more than one observation needed be made for numerous samples in order to fully comprehend the phenomenon being seen.

a. Dissecting Microscope

The dissecting microcopy, also called as the stereoscopic microscope, employs light reflected from an object rather than light transmitted through it and is used for low magnification views with a sense of three-dimensional depth. Due to the utilization of two separate and distinct optical channels together with two objectives and eyepieces, the device can offer somewhat varied viewing angles for the left and right eyes. Macrophotography is used in conjunction with this microscope to record and examine solid specimens. They are frequently employed in the manufacturing sector for fractography and forensic engineering in inspection and quality control, as well as for researching insect surfaces and the surfaces of solid objects.

In-text: (“Stereo microscope”, 2020)

 Bibliography: Stereo microscope. En.wikipedia.org. (2020). Retrieved from https://en.wikipedia.org/wiki/Stereo_microscope.

a. Dissecting Microscope

The dissecting microcopy, also called as the stereoscopic microscope, employs light reflected from an object rather than light transmitted through it and is used for low magnification views with a sense of three-dimensional depth. Due to the utilization of two separate and distinct optical channels together with two objectives and eyepieces, the device can offer somewhat varied viewing angles for the left and right eyes. Macrophotography is used in conjunction with this microscope to record and examine solid specimens. They are frequently employed in the manufacturing sector for fractography and forensic engineering in inspection and quality control, as well as for researching insect surfaces and the surfaces of solid objects.

In-text: (“Stereo microscope”, 2020)

 Bibliography: Stereo microscope. En.wikipedia.org. (2020). Retrieved from https://en.wikipedia.org/wiki/Stereo_microscope.

Light microscopy is only one way of looking at cells. Scientists also use dissecting microscopes and electron microscopes. Define each type of microscope and explain how they work and what they are used for. You might need to search the internet for this. Don’t forget to reference any sources you use. 100 words minimum for each.

b. Electron Microscope

Using a beam of accelerated electrons that is focused by magnetic lenses, which are shaped magnetic fields that form electron optical lenses, the electron microscope is a microscope with extremely high power that is used for high resolution images. Because electrons have a wavelength that is 100,000 times shorter than photons of light, they have a higher power and can therefore reveal the structure of smaller objects. These microscopes study ultrastructures and are employed in the manufacturing sector for quality assurance and control. SEM and TEM are the two primary categories of electron microscopes; the former can be used to observe the inner of cells, while the latter can be used to view the cell surface in detail.

In-text: (“What is Electron Microscopy? – UMASS Medical School”, 2020)

Bibliography: What is Electron Microscopy? – UMASS Medical School. University of Massachusetts Medical School. (2020). Retrieved from https://www.umassmed.edu/cemf/whatisem/.

In-text: (“Definition of electron microscope | Dictionary.com”, 2020)

Bibliography: Definition of electron microscope | Dictionary.com. www.dictionary.com. (2020). Retrieved from https://www.dictionary.com/browse/electron-microscope.

In-text: (“Electron microscope”, 2020)

Bibliography: Electron microscope. En.wikipedia.org. (2020). Retrieved from https://en.wikipedia.org/wiki/Electron_microscope.

b. Electron Microscope

Using a beam of accelerated electrons that is focused by magnetic lenses, which are shaped magnetic fields that form electron optical lenses, the electron microscope is a microscope with extremely high power that is used for high resolution images. Because electrons have a wavelength that is 100,000 times shorter than photons of light, they have a higher power and can therefore reveal the structure of smaller objects. These microscopes study ultrastructures and are employed in the manufacturing sector for quality assurance and control. SEM and TEM are the two primary categories of electron microscopes; the former can be used to observe the inner of cells, while the latter can be used to view the cell surface in detail.

In-text: (“What is Electron Microscopy? – UMASS Medical School”, 2020)

Bibliography: What is Electron Microscopy? – UMASS Medical School. University of Massachusetts Medical School. (2020). Retrieved from https://www.umassmed.edu/cemf/whatisem/.

In-text: (“Definition of electron microscope | Dictionary.com”, 2020)

Bibliography: Definition of electron microscope | Dictionary.com. www.dictionary.com. (2020). Retrieved from https://www.dictionary.com/browse/electron-microscope.

In-text: (“Electron microscope”, 2020)

Bibliography: Electron microscope. En.wikipedia.org. (2020). Retrieved from https://en.wikipedia.org/wiki/Electron_microscope.

.

We have been contacted by WMU’s Department of Blindness and Low Vision Studies. They are the premier program in the nation and they want to equip their students so that they can participate in biology labs and become biology professionals. Using what you have learned about observation in the first lab, I would like you to think about a way to use another of your senses to observe nature if you were blind. Ask yourself, how would you gain information through observation if you could not see?

As a result of what we have learnt about observation, we are aware that all of our senses should be used when doing an observation. We will therefore employ a method that only requires thinking, touching, and hearing in order to enable those pupils to engage in and experience using a microscope in a lab. Three different types of microscopes will be set up first. The first is a stereoscopic microscope, followed by a scanning electron microscope and, finally, a transmission electron microscope. Because this experiment does not need vision, a rope must be passed through each station before a student may complete the lab. Depending on the material that will be given to the student and the type of microscope required, Before the intersection of the three microscopes, the guidance rope must have a button that, when pressed by the learner, will activate a recorded voice that will direct them in the right path until they reach their destination. When you get to the microscope, there should be another button there on the guiding rope as well. When this button is pressed, a computerized voice asks you for the password and sample that have been assigned by the instructor. Once you enter the information correctly, an automatic system places the slide over the microscope and the microscope begins scanning the slide, but instead It has all of the labeling information in Braille alphabets, allowing the student to experience working in a lab by being able to see the shape and structure of a cell’s interior or external components without the need of their eyes. To avoid any mistakes, this operation should be carried out under the instructor’s close supervision. My justification for employing this approach is that, similar to the Braille system, 3D printing may offer a real experience of what a cell looks like or what is inside that specimen.

When you have thought this through and come up with a way of observing without seeing, please describe your method that could be used in a biology classroom (or in the field) to observe if you were blind. To get all of the points for this question you will need to describe your method so that others can understand what you intend to do. You also need to provide a rationale as to why you think this method will do what you believe it will. 300 word minimum.

I want you to write an essay below that explains/describes what you have learned through and about observation during your first laboratory exercise. Incorporate into the essay the light microscope, electron microscope, plant cells, and animal cells. Simply describing what you saw and read is not sufficient; you need to explain the learning that occurred and how you arrived at your new understanding. I want to observe your learning and growth that occurred because you did this series of simulations and assignments. An example that you could use includes how similarities and differences in cell anatomy led you to a better understanding of cells and what each type contains. But do not be limited by this example! I am interested in what insights YOU gained by observing. 500 word minimum.

What I took away from this lab about observation is that it involves more senses than just sight. This was demonstrated in the procedure laid out for the students with special needs; even though they were unable to use their eyesight to visualize the samples, they were still able to do so using their other senses. Additionally, I discovered that observation involves focus. Every day, we observe numerous things that happen around us but are oblivious of them. If we were to observe these things closely, however, we would begin to notice new things that we are unaware of, such as animal cells. but if we take a sample of human skin and carefully examine it using a stereoscopic microscope first, we would find intriguing information, which makes us intrigued and we start watching further by studying the sample under a SEM and TEM, then we can see the inner and outer surface of the cells, and this is one of the fundamental foundations of research that is based on observation; without observation, we would not have reached the level that we are in today. Plants can be thought of in the same way that humans can, and we see them all the time. However, based on what was said, understanding how plants work can only be achieved by looking at a sample of these plants under a microscope. Since most scientific advancements wouldn’t have been possible without first carefully examining the natural events all around us, I’ve come to believe that observation is the root of invention. I also realized that observation is greatly aided by curiosity and a constant desire to know what is happening in the world. Moreover, through observation, I was better able to comprehend how the functioning of plants differs from that of animals and how these differences and similarities are crucial in classifying various domains based on the elements of cells and the degree of complexity. Another thing is that after comparing animal and plant cells, I was able to clearly see how variation and adaptation are related. For instance, when comparing animal and plant cells, we can see that plant cells have a vacuole and a cell wall, whereas animal cells lack these features. When we apply this knowledge to the observation of plants in the natural world, we can see that because they spend most of their lives in practically one place, only sometimes moving to adapt to changing weather conditions, they possess the rigidity necessary to maintain their position while remaining stationary, whereas animals lack these traits because they are the complete opposite and are constantly on the move in search of food and shelter. Finally, I realized that the major motivation behind the development of the microscope was the necessity to conduct more observation in order to produce novel discoveries.