SQL, ER Modelling and Relational Database Model

SQL and ЕR Modeling and Relational Database Model

Module Code:

Academic Year: 2015/2016

Table of Contents

Entity – Relationship Diagram (E R Diagram) 2

Assumption: 5

Query Transactions 5

Integrity constraints violated 8

References 9

Entity – Relationship Diagram (E R Diagram)

doctor other specialization. other qualifications and , doctor’s specialization, doctor’s qualifications Doctors gender, doctor’s date of birth,Entity – Relationship Diagram shows relationship between various entities within the organization. In this case the identified identities include doctors in register entity, Treatments details entity, patient entity, consultation details entity and payment entity. Let us assume doctors_in_register the following has Doctor number(ID), doctor’s name, doctor’s centre in our case study. A well managed database for medical centre will reduce paper and will be easy to follow by all staff of the centre. The main purpose of Entity – Relationship Diagram is create data base that will have in database management of any organization. This kind if diagram will be helpful to hospitals like medical

medicare number (mn), individual reference number (IRN) private health cover number and private health cover company. Phone_Number, address, date of birth, gender, For the Patient_Details entity has the following attributes Patient_Serial number (ID), Patient _name, Patient_

medicine and other treatments_Details entity the group has treatmentFor thename, dosage, number of repeats, and special instructions(SI). _Details entity the group the following Prescription. For the special remarks symptoms and Description, consultation _time, consultationdate,
_naunber consultationconsultation_Details entity group has the following sets of related data, consultationthe T

SQL, ER Modelling and Relational Database Model

SQL, ER Modelling and Relational Database Model 1

Query Transactions

1). In orders to display books with the title Database and arranged in alphabetic order, the following query is used;

LIBRYS_BOOKS_DETAILS in alphabetic order where book_ title =’Databases’. Display * titles_ in_

2. this section requires the retrieval of books number of books and number of copies. The query is written as

DataBases’ *number of books_in_ LIBRYS_BOOKS_DETAILS where CATEGORY= ’a). display

DataBases’ *number of copies_in_ LIBRYS_BOOKS_DETAILS where CATEGORY= ’b). display

3. the main purpose is to be able to write a query for showing first name and surname of authors of books in case the book title is «DataBases»

1. we will begin with displaying information which does not have NATURAL JOINs, therefore the query is;

DataBases’Display* authors first_name, Last_name from , LIBRYS_BOOKS_DETAILS where CATEGORY=’

2. in this case it is displaying the same information but without NATURAL JOINs.

DataBases’Display* authors first_name, Last_name from , LIBRYS_BOOKS_DETAILS where CATEGORY=’

4. in this case is to be able to write a query about one specific book. Here the query is about American Electrician’s Handbook translator showing his names from first name, other names, and surname.

American Electrician’s Handbook’ first_name, middle_name Last_name from , LIBRYS_BOOKS_DETAILS where CATEGORY=’translatorSelect *

5. the query is about showing people who borrowed books abd were late to return. It displays their first name and surnames.

returned books late’ first_name, Last_name from , LIBRYS_BOOKS_DETAILS where CATEGORY=’borrowerDisplay*

6. There is a category of borrowers who were late more than 7 days and want to have their first and surnames, therefore the query will be as

returned books more than 7 days late’ first_name, Last_name from , LIBRYS_BOOKS_DETAILS where CATEGORY=’borrowerDisplay*

7. The library books be death stocks that is they have never boon borrowed by anybody. This information will be displayed as shown below;

LIBRYS_BOOKS_DETAILS where category=’Never Been Borrowed’. Query: display * from titles_in_

8. The quesry to be desiged here will help a borrower get alternative books when the title selected is not in the library either it is borrowed or is out of stock. The book to borrowed is PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS.

The first query is enable the borrower get alternative books in the field of dababase.the query will be written as shown below .

’.«DATABASELIBRYS_BOOKS_DETAILS where category =Query: display * from tittles_in_

PRINCIPLES AND PRACTICE OF DATABASE SYSTEMS»LIBRYS_BOOKS_DETAILS where category= “Query: display * titles_in_2. in this second case is to enable the borrower have books written by the same author. This means the books may not be the same field but same author. The query will be as follows;

9. The main purpose of this query to display all publishers who have published books within the subject «DataBases». The query written should have the ability to display the published book and the publisher’s full name. These publishers may have published books in other subjects, too. However, your query should only display book titles in DataBases subject.

Select publisher_full name, book_titles, from Library_books where Books category =’Databases’

10. The main purpose of this query to display all publishers who have not published books within the subject «DataBases». The query written should have the ability to display the published book and the publisher’s full name.

Select *publisher_full name, book_titles, from Library_books where Books category =’All books excluding Databases’

Integrity constraints violated

Table Constraint and Column Constraint1). The information has

ntegrity constraints that has been violated the programmer should select the last row.iIn order to eliminate this

SQL, ER Modelling and Relational Database Model 2

.PROJECT2). Insert <‘ProductA’, 3, ‘Bellaire’, 2> into

No violations

SQL, ER Modelling and Relational Database Model 3

3. Insert <‘Production’, 4, ‘943775543’, ’01-OCT-88′ > into DEPARTMENT.

No violations

SQL, ER Modelling and Relational Database Model 4

.WORKS_ON4. Insert <‘123454321’, null, ‘40.0’> into

No violations

SQL, ER Modelling and Relational Database Model 5

.DEPENDENT5. Insert <‘453345453’, ‘John’, M, ’12-DEC-60′, ‘SPOUSE’> into

No violations

SQL, ER Modelling and Relational Database Model 6

= ‘Joy’.dependent_nametuples with DEPENDENT 6. Delete the

Table Constraint- the other name and number will be added

SQL, ER Modelling and Relational Database Model 7

= ‘888665555’.SSNtuple with EMPLOYEE 7. Delete the

Table Constraint- Nane of employees will be added

SQL, ER Modelling and Relational Database Model 8

=’123456789′ to ‘666884444’.SSNtuple with EMPLOYEE of the SUPER_SSN 8. Modify the

No violations

References

. New York, Plenum Press.Dealing with complexity: an introduction to the theory and application of systems science’Flood, R. L., & Carson, E. R., 2009, ‘

4th edn, Pearson Education, IndiaModern System Analysis and Design,Hoffer, JA, George, JF, Valacich, JS &Panigrahi, PK 2005,

9th edn, Course technology, MA, USASystem analysis and Design, Shelly, GB & Rosenblatt, HJ 2011,

Silverston, L., Inmon, W.H. & Graziano, K. 2007. The Data Model Resource Book. New York: Wiley, 1997.

Whitten, Jeffrey L.; Lonnie D. Bentley, Kevin C. Dittman. 2004. Systems Analysis and Design Methods. New York: Wiley.