MODUL 3

MODULE 3 

DATABASE DESIGN 

USING E-R DIAGRAM MANUAL 

A. Purpose
1. Students are able to design a database through its design stages.
2. Students are able to realize the results of database design into ER diagrams manually.
B. Basic Theory
The database can be modeled as:
1. Set of entity (entities)
2. The relationship between the entity (entities)

•  Entity Sets 

Entity is an object which can be recognized from another object. Example: someone special, corporate, plant, and others.

• Attributes 

Entity is shown by a set of attributes, which descriptive of its property owned by all members of the entity set.
Type attribute:
1. Simple and composite attributes
2. The function and multi-function
3. Origin attribute

• Relationship Sets 

Relationship is compatibility among multiple entities. Relationship set is a mathematical relationship between the entity n> 2, each part is taken from the unit entity.

• Depth Relationship Sets 

Refers to the number of entities involved in the relationship sets sets. Relationship sets that involve two entity sets are binary (or degree two). Generally, almost all relationship sets in a database system are binary.

• Mapping cardinalities (Cardinalitas Mapping) 

Capture the number of entities to which another entity can be associated via a relationship set. Mapping Cardinalitas most widely used in describing binary relationship sets. For a binary relationship set cardinalitas mapping must be one of the following types:
1. One to one (one to one)
2. One to many (one to many)
3. Many-to-one (many to one)
4. Many to many (many to many)

• ERD : Entity Relationship Diagram

Reflecting database models : relations between entities ( tables ) and relationships ( relationships ) between the entities .
1 . Rectangles symbolize sets of entities .
2 . Diamonds symbolize relationships .
3 . Lines connecting attributes with entity sets and entity sets to relationship sets ( relationship) .
4 . Ellipses represent the attributes .

•  Rules for Model Database

1 . Each line must stand alone .
2 . Each line must be unique .
3 . Columns must stand alone .
4 . Value of each column must be a unity .

• Database Creation Phase

1 . Stage 1 : Define Entities
The properties of the entity :
a. significant
b . general
c . fundamental
d . Unitary
2 . Phase 2 : Determine the Attributes
Determine the attributes ( properties ) of each entity database as needed :
a. Determine the properties ( fields or columns ) that was owned by the entity , as well as its data type .
b . Corresponding attribute must :
1 . significant
2 . direct
c . Determine which became a primary key attribute for the entity in question .
d . If the attribute is not enough , some attributes can be combined Composite Primary Key .
e . If Composite Primary Key should add a lot of artificial attribute into a single Primary Key .
3 . Step 3 : Determine Relationships
Determining the relationships between entities :
a. Determine the type of relationship between one entity to another entity .
b . There are three kinds of relationships :
1 . One to one ( 1:1 )
2 . One to many ( 1 : n )
3 . Many to many ( m : n )
c . In forming a relationship between two entities , specify the attribute name that is used to connect the two entities .
d . Determine which entity becomes the main table and which became the second table .
e . Attribute ( from the main table ) that connect the two tables into a Foreign Key in the second table .
4 . Stage 4 : Making ERD
a. Create Entity Relationship Diagram ( ERD ) based on the results of Phase 1-3 .
b . There are various notations for ERD -making .
c . Using special software to draw ERD .
5 . Stage 5 : The process of database normalization
6 . Stage 6 : Implementation Database
C. Equipment and Materials
1 . The computer with the Windows 7 operating system .
2 . E-draw Mind Map software application 1 .
3 . Practical Module System Database .
D. Step Work
A database software company requested make database that will handle the data bank . The data will be handled are : personal data about customers , the data deposit accounts owned by customers , bank branches where customers opening deposit , and conducted customer transaction data . Customers may have more than one account deposits , and the deposit account can be owned by more than one customer at a time ( joint account ) .
Steps banking database design :
1 . Determining entities ( basic objects ) that need to exist in the database .
· Client : store all the personal data of all customers .
· Account : stores information of all accounts that have been opened .
· Cabang_bank : store information about all branches of the bank .
· Transaction : store information about all the transactions that have occurred .
2 . Finding the attributes ( properties ) of each entity database as needed .
· Customers :

• id_nasabah : id number for the customer ( integer ) PK
•  nama_nasabah : client 's full name ( varchar ( 45 ) )
• alamat_nasabah : full address of the customer ( varchar ( 255 ) )

· Account :

•  no_rekening : account number ( integer ) PK
•  pin : personal identification number ( varchar ( 10 ) )
•  balance : the amount of the account balance in U.S. $ ( integer )

· Cabang_bank :

•  kode_cabang : code for a bank branch ( varchar ( 10 ) ) PK
•  nama_cabang : full name of the bank branch ( varchar ( 20 ) )
•  alamat_cabang : full address of the bank branch ( varchar ( 255 ) )

· Transaction :
o no_transaksi : transaction number ( integer ) PK
o jenis_transaksi : credit or debit ( varchar ( 10 ) )
o date : the date of the transaction ( date )
o number : the amount of the transaction in USD ( integer )
3. Determine relationships (relationships) between the entities.

relationship
· Customers have accounts:
o The main tables: customer, account
o The second table: nasabah_has_rekening
o Relationship: many to many (m: n)
o Attribute liaison: id_nasabah, no_rekening (FK id_nasabah, no_rekening in nasabah_has_rekening)
· Customer makes a transaction:
o The main table: customers
o The second table: transactions
o Relationship: one to many (1: n)
o Attribute liaison: id_nasabah (FK id_nasabah in the transaction)
· Cabang_bank handle account:
o The main tables: accounts
o The second table: transactions
o Relationship: one to many (1: n)
o Attribute liaison: no_rekening (FK no_rekening in the transaction)
· Accounts involved in the transaction:
o The main tables: accounts
o The second table: transactions
o Relationship: one to many (1: n)
o Attribute liaison: no_rekening (FK no_rekening in the transaction)
4. Drawing E-R diagrams manually:
in this picture I use Lucidchart (u can open in Lucidchart.com)

E. Analysis
From the above experiments, a variety of benefits can be obtained from the manufacture of ER Diagram (ERD). With ERD, can make it easier for us to see relationships between entities as well as modeling the data structures and relationships between data. ERD can also be used to document the existing data by identifying each entity and relation.
F. Duties
1 . Make design a database to handle data lectures . The data will be handled are : personal data about students , faculty personal data concerning the data subject and the data classrooms . Students may take more than one course , and one course may be taken by more than one student at a time ( joint account ) .
Draw ER Diagram manual for the case of stage 1 to stage 4 !
Design steps :
1 ) Determine the entity :
· Student : store all the personal data of all students .
· Faculty : store all personal data arbitrarily lecturer .
· Mata_kuliah : store information about all the existing courses .
· Ruang_kelas : store information about all classrooms are used .
2 ) Determine the attributes :
· Student :
o nama_mahasiswa : student 's full name ( varchar ( 50 ) )
o NIM : student registration number ( char ( 10 ) ) PK
o address : student residential address ( varchar ( 255 ) )
o tempat_lahir : place of birth of students ( varchar ( 20 ) )
o tgl_lahir : date , month and year of birth of students ( varchar ( 20 ) )
· Faculty :
o nama_dosen : full name lecturers ( varchar ( 50 ) )
o NIK : faculty personnel identification numbers ( varchar ( 20 ) ) PK
o address : home address lecturer ( varchar ( 255 ) )
o no_HP : lecturers phone number ( integer )
· Mata_kuliah :
o nama_mk : course name ( varchar ( 40 ) )
o kode_mk : the course code ( varchar ( 15 ) ) PK
o jumlah_sks : the number of course credits ( integer )
o classes : classes and hours in a course ( varchar ( 2 ) )
o schedule : college course schedule includes days and hours ( varchar ( 20 ) )
· Ruang_kelas :
o id_kelas : class identities such as building , floor and room number ( char ( 5 ) ) PK
o capacity : the number of seats or the maximum capacity that can be accommodated ( integer )
3) Determine the relationship between entities:

relationship: 

· Students are guided by faculty: 

•  Table main: professor 
•  Table two: student 
•  Relationship: many to one (n: 1) 
•  Attribute liaison: NIK (NIK in faculty FK) 

· Students take mata_kuliah: 

•  The main tables: students, mata_kuliah 
•  The second table: mhs_ambil_mk 
•  Relationship: many to many (m: n) 
•  Attribute liaison: NIM, kode_mk (FK NIM, kode_mk in mhs_ambil_mk) 

· Faculty administer mata_kuliah: 

•  The main table: mata_kuliah 
•  The second table: lecturer 
•  Relationship: many to one (n: 1) 
•  Attribute liaison: kode_mk (FK kode_mk in mata_kuliah) 

· Mata_kuliah occupy ruang_kelas: 

•  The main table: ruang_kelas 
•  The second table: mata_kuliah 
•  Relationship: 1:1 
•  Attribute support: id_kelas (FK id_kelas in ruang_kelas)

4) Draw ER diagram:

in this picture I use Lucidchart (u can open in Lucidchart.com)

2 . Take for example any database ( should be different for each student ) . Make the design of the database manually ER Diagram from stage 1 to stage 4 , provided at least database contains 4 pieces entity .

I will create a database Mirai ( Private & Sistematic ) . Mirai is the institute courses ( les ) for levels kindergarten , elementary , junior high , and high school . Subjects provided are Mathematics and English . The system used is a private , one tutor a student . Additionally Mirai teach students according to their ability , to the existing levels divide students by ability level of each student . There are different , even though using the private system , Mirai require that students come to tutoring which has been provided by Mirai . For each student who took the course , students will receive tutoring hours right twice in a week with the designated day . Students are allowed to take the schedule as desired ( any subject area ) , but depending on tutor availability and place . Mirai has been an expert tutor in the two subjects at once ( Maths and English ) .

The data that will be addressed are : personal data about students Mirai , personal data about tentor , data about subjects , data on a schedule provided by Mirai . Database design steps Mirai :

1 . Determine the entity :

· Students : store all data about students Mirai .

· Tutor : store all data about Mirai tutor .

· Maple : store information about subjects in Mirai .

· Schedule : stores information about the tutoring schedule provided Mirai .

2 . Specifying attributes :

· Students :

•  nama_siswa : Mirai student 's full name ( varchar ( 50 ) )
•  id_siswa : Mirai student id number ( integer ) PK
•  alamat_siswa : student residence address ( varchar ( 255 ) )
•  classes : a class of students at the school formal ( integer )
•  nama_ortu : the name of the parents or guardians of students ( varchar ( 50 ) )
•  no_HP : phone number / mobile students who could be contacted ( integer )

· Tutor :

•  nama_tentor : full name tentor Mirai ( varchar ( 50 ) )
•  id_tentor : Mirai tutor id number ( integer ) PK
•  address : tentor residential address ( varchar ( 255 ) )

· Maple :

•  kode_mp : subject code ( integer ) PK
•  nama_mp : subject name ( varchar ( 15 ) )
•  levels : level subjects in Mirai , ie from level 1-12 ( integer )
•  costs : the cost in dollars of subjects ( integer )

· Schedule :

•  hari_jam : days and hours of tutoring ( varchar ( 15 ) ) PK
•  tempat_les : Mirai tutoring place that symbolized with numbers ( integer )

3 . Determine relationships between entities :

relationship: 

 students mentored tutor: 

•  The main tables: students 
•  The second table: tentor 
•  Relationship: one to one (1:1) 
•  Attribute liaison: id_siswa (FK id_siswa in students) 

 The students took the subject: 

•  The main tables: students, maple 
•  The second table: siswa_mapel 
•  Relationship: many to many (m: n) 
•  Attribute liaison: id_siswa, kode_mp (FK id_siswa, kode_mp in siswa_mapel) 

The students choose the schedule: 

•  The main tables: students, schedule 
•  The second table: siswa_jadwal 
•  Relationship: many to many (m: n) 
•  Attribute liaison: id_siswa, hari_jam (FK id_siswa, hari_jam in siswa_jadwal)

4. Draw ER Diagram manual:

in this picture I use Lucidchart (u can open in Lucidchart.com)