Question 2 (EER)

weekly reflection 2

CHAPTER 3:

A system is a set of components that interact with each other to form a whole and work together toward a common goal.

Components of information system are database, people, procedures, software, hardware and data.

5 phase of System Development Life Cycle (SDLC):

• Preliminary study phase
• Systems analysis phase
• Systems design phase
• System implementation phase
• Maintenance phase

4 goals of database development are:

Ø  Develop a common vocabulary

Ø  Define the meaning of data

Ø  Ensure data quality

Ø  Find an efficient implementation

 6 phase of database life cycle (DBLC):

      The database initial study

      Database design

      Implementation and loading

      Testing and evaluation

      Operation

      Maintenance and evolution

Two types of skills are needed in database developments which are the soft skills and the hard skills.

weekly reflection 1

CHAPTER 1:

Database system have taken us from a paradigm of data processing in which each application defined and maintained its own data to one in which the data is defined and administered centrally.

Database is a collection of related data. Database characteristics are persistent, shared, and interrelated.

Database system is a system with a database, a DBMS and a full description of the structure and constraints of the data stored in the database.

Database management system (DBMS) is a software system that enables users to define create, and maintain the database and providers control access to this database.

Functions of Database management system:

• Data storage, retrieval and update.
• Data dictionary management.
• Transaction support.
• Multiuser access control.
• Backup and recovery management.
• Security management.
• Support for data communication
• Data integrity management.
• Services to promote data independence.
• Utility services.

The advantages of Database management system:

§  Control of data redundancy

§  Data consistency

§  Access versatility

§  Program and file independence

§  File consolidation.

§  Improved security

§  Improved data integrity

Three schemes supported by client server architecture are:

Ø  Client, server and database on the same computer.

Ø  Multiple clients and one server on different computers.

Ø  Multiple servers and database on different computers.

Two types of Data Independence:

v  Logical data independence

              Protection from changes in logical structure of data.

v  Physical data independence

              Protection from changes in physical structure of data.

Three levels of database system architecture

ü  External level: users’ view of the database

ü  Conceptual level: Community view of the database        

ü  Internal level: physical representation of the database on the computer

The 6 components of DBMS

1. Query  processor
2. Database manager
3. File manager
4. Data manipulation language processor
5. Data definition language compiler
6. Dictionary manager

CHAPTER 2:

Specialization:  A specialized kind of entity set may be derived from a given entity set.

Generalization:  Several entity sets can be abstracted by a more general entity set.

Categorization is the modelling of a single subclass with a relationship that involves more than one distinct superclass.

The relational data model has one data structure, the relation. The relation is a restricted table.

Relations are defined in terms of attributes, tuples, primary keys, foreign keys and domains.

A special character exists in relational systems for representing incomplete or missing information.-null

The model also has two inherent integrity rules: entity and referential integrity and enterprise constraints.

Normalization is a process for assigning attributes into table and reduces data redundancy and helps eliminate the data anomalies that associated with poor database design (unnormalized).

Four levels of normalization:

• 1NF –a relation where the intersection of each row and column contains no repeating values. (only one values)
• 2NF –a relation that is in 1NF and every non-key primary key attribute is fully functionally dependent on primary key.
• 3NF –a relational based on the concept of transitive dependency.
• BCNF –a relational based on the concept of the determinant.

Question 1 (EER)

MYSQL COMMAND (ANSWER)

a)Create database Raju_Clinic;
b)Create table Patient(PatientNo varchar (4) primay key,
                       Patient_IC varchar (12),
                       Patient_Name varchar(50),
                       Patient_Address varchar(100),
                       Patient_Mobile int (12)) engine = innodb;

  Create table Treatment(TreatmentID varchar (4) primary key,
                      Treat_Date  date,
                      Treat_Time time,
                      PatientNo varchar (4)
                      foreign key (PatientNo) references
                      Patient (PatientNo) engine = innodb;

 Create table  Doctor (DoctorID varchar (4) primary key,
                      Doctor_Name varchar (50),
                      Type of sick varchar (100),
                      Medication varchar (100),
                      TreatmentID varchar (4)
                      Foreign key(TreatmentID)references
                      Treatment (TreatmentID)) engine = innodb;

Create table    Pharmacy (PharmacyNo varchar (4) primary key,
                      Price int(5),
                      Recipe_Date date,
                      Doctor_ID varchar(4),
                      foreign key(DoctorID)references
                      Doctor(DoctorID))engine = innodb

c)show databases;

d)show tables;

e)describe Patient;
   describe Treatment;
   describe Doctor;
   describe Pharmacy;

f)insert into Patient(PatientNo,Patient_IC,Patient_Name,Patient_Address,
                     Patient_Mobile)
                     values(''P001'',''890521056889'',''Ahmad'',''Jalan Merbau'',
                     ''012-4567896'');

  insert into Patient(PatientNo,Patient_IC,Patient_Name,Patient_Address,
                     Patient_Mobile)
                    values(''P002'',''780316015686'',''Rita'',''Jalan Jati'',
                   ''013-6548576'');

g)select * from Patient;
   select * from Treatment;
   select * from Doctor;
   select * from Pharmacy;

h)drop Patient
   drop Treatment
   drop Doctor
   drop Pharmacy