|
A system is an
integration of entities, also called components which have an interaction
among them.
|
For example, entities of
the department of a university system are
|
® Students
|
® Faculty
|
® Non-teaching staff
|
® Class rooms
|
® Computer center
|
® Furniture
|
® Teaching methodologies
|
These entities interact
with one another for the functioning of the department.
|
Terminology of a file :-
|
A file consists the
keyboard such as fields, records, file, primary key filed, secondary key field
& non-key fields.
|
Field A field is the
lowest level of the data item of an entity. It is also known as an attribute
in database atermindogy. Thus each & every attribute is defined to store
data in the fields.
|
Recard/Row:
A record is the collection of all the fields (attributes) of an entity in a
desired sequence.
|
File:
A file is a collection of records / rows having the set of fields arranged in
a sequence
|
Key field (or) Primary key: -
|
A field is said to be a
key field (or) primary key(PK). If it can be a identified a record uniquely
in a file.
|
Ex:- Stu-code in a stu-file
|
Emp-code in a emp-file
|
Thus their will be only
one record in the respective file having the specified key filed. The primary
key is generally indicated with an under score.
|
S.No
|
Item code
|
Name
|
Item-quantity
|
Item order
|
Recorder level
|
Maximum level
|
Minimum level
|
|
Primary key
|
Inventory file
|
Secondary key/non-key fields:
|
A field is said to be
non-key field or secondary key field, if it cannot identify a records in the
file.
|
Associations between the fields:
|
Association between the
fields of a file is a relationship interms of occurens of data of the file.
The associations are classified into 5 types.
|
(1) One-to-one
association (1 : 1 «)
|
(2) One-to-many
association (1:M«)
|
(3) Many-to-many
association (M:M «)
|
(4) Many-to one
association (M : 1 «)
|
(5) One-to-one
conditional association (¬o®)
|
One –to–one Association :
A record of one file is associated with only one record of another file
|
Tem
|
Item-code
|
Desc
|
EOQ
|
Stock
|
Rol
|
|
|
|
|
|
This is the example of storing item
in a row material stores. In this store there will be a specific area of each
item storage space in the stores are in item & space the details of the
item &storage space in the stores are in item & space files are in 1
: 1 relation. If means each & every record of the item file is associated
with only one record of space file
|
One to many association. In this
type of association a record of one file will be associated with one (or)
more records of another file
|
Teacher code
|
Name
|
Years of experience
|
Specialization
|
|
Subject
1 : m
|
Student code
|
Name
|
Max mark
|
|
Here the association from the
faculty file to the subject file is one-to-many. Each faculty can teach one or more subjects
& therefore each record of the faculty file is associated with one or
more subject files.
|
Many to many association : In many
to many association each & every record of one file will be associated
with one-or-more records of the another file student.
|
Student
|
Student code
|
Name
|
Specialization
|
Sem avg marks
|
|
m:m
|
Subject
|
|
|
Each & every record of the
student file is associated with one or more records of the student file.
Because each student will opt for one or more subjects in a semester.
Similarly each & every record of the subject file is associated with one
or more records of the student file. Because each subject will be attended by
at least one student.
|
Here it is very difficult to include
individual subject marks which maker it to work with these type of
association. Therefore the many to many association is again converted into
two different one-to-many associations as shown below.
|
Student
|
 Student code
|
Name
|
Specialization
|
Sum-avg marks
|
|
Enrollment
|
Student code
|
Subject code
|
Marks
|
|
Subject
|
Subject code
|
Subject name
|
Max-marks
|
|
Many to one association:
|
Many records of the first five will
be associated with a single record of the 2nd file.
|
Faculty
|
 Teacher code
|
Name
|
Specialization
|
Years of experience
|
|
Subject
|
Student code
|
Subject code
|
Specialization
|
|
When we need the association from
the subject file to the faculty file then the association is many to one. If
means many records of student file will have one record of faculty file .
|
One-to-one conditional association:
|
A particular record of a file will
be associated to only one record of another file for only certain period
temporally.
|
Room
|
room no
|
Room type
|
Patient no
|
Location
|
|
Patient
|
Patient no
|
Name
|
Address
|
Data admitted
|
|
A record of the room file will have
association with one record of the patient file as long as the patient stays
in that room. Otherwise the record of the room file will not have any
association with the patient file.
|
Multiple association:
|
There will be more than one type of
association between two files in some situations.
|
|
|
|
Consider an example of stock control
in a row material stores. Whenever there is a request, the required item is
issued as per the quantity demanded & the stock on hand on that item will
be reduced accordingly. Similarly when there is an arrival of material
against the purchase order then the stock on hand of that item will be
increased.
|
SOH = SOH – Quantity of issue (on
issues)
|
SOH = SOH + Quantity f receipt (on
receiving material/stock)
|
Note that it is very difficult to
implement
|
Files with multiple association
therefore multiple association. There fore it can be modified in a relation
as
|
Item Reorder
level
|
Item code
|
Desc
|
EOQ
|
SOH
|
ROL
|
|
Economic order quantity
|
Transaction
|
|
|
