a database sys.:- is basically just a computerized record-keeping sys. the database itself can be regarded as a kind of electronic filing cabinet that is, it is a repository or container for a collection of computerized data files. users can perform a variety of operations involving
- adding new files to the db.
- inserting data into existing files.
- retrieving data from existing files.
- deleting data from existing files.
- removing existing files from the db.
a db is a collection of related and organized info., the db is a structure object consist of data and meta data. the data in a db is the actual stored descriptive info., such as a name, address of student. meta data describes the structure applied by a db to the student data. in other words, the meta data is the student table definition. the student table definition contain the fields for name and address, the length of each field and data type. meta data applies structure and organization of raw data with meta data to being the structure part of the db contains both meta data and raw data.
any db sys involve four major components: data, h.w, s.w, and users. a simplified picture of a database is shown in the figure below:
databa management system(dbms)
|
databa management system(dbms)
|
databa management system(dbms)
|
application programs end users
simplified picture of a database system
the data in the db must be integrated and shared.
by integrated means the db can be thought of as a unification of several otherwise distinct files, with any redundancy among those files partly or wholly eliminated.
the shared, the db can be shared among different users, in the sense that different users can have access to the same data at the same time.
the hardare components of the sys consist of.
the software, between the physical db itself: the data as physical stored and the users of the sys is a layer of software, known as the db manager or db server or most commonly the db manager sys (dbms). all requests for access to the db are handled by the dbms.
users there are several classes of users some of them:-
· application users: responsible for writing db application programs in some programming language such as java, c++,..etc.
· end users who access the db interactively.
· db administrator (dba) is the technical person for implementing the data administrator decisions its job:
v decide what data should be stored in the db in the firace.
v establish policies for maintaining and dealing with that data once it has been stored.
v create the actual db and to put in place the technical controls needed to enforce the various policies decisions made by the da.
v responsible for ensuring that the sys operates with adequate performance and for performing other technical services.
the data administrator (da) an identifiable person in the enterprise who has centralized control on the data. since the data is one of the enterprise most valuable assets, it is imperative that there should be some person who understands that data, and the needs of the enterprise with the respect to that data, at a senior management level, the da is that person, he is a manager, not a technician.
benefits of the db approach
· the data can be shared: sharing not only for the existing applications can share the same data in the db, but new application can be developed to use the same data without having to add new data to the db.
· redundancy can be reduced: in non-db sys each application has its own files. this fact can often lead to redundancy in stored data and waste in storage space. this can be avoided as long as the da is aware of the data requirements for the applications and control on them.
· inconsistency can be avoided: if there are two entries for some person these entries has the same info. on that person but in different places, one entry is updatingd the other is not at such time the db is said to be inconsistent. db is on an consistent state is capable of supplying incorrect or contradictory info. to its users
· transaction support cab be provided: a transaction is a logical unit of db, typically involving several db operations.
· integrity can be maintained: integrity is the problem of ensuring that the data in the db is correct. this problem can arise only if redundancy exists in the stored data. even if there is no redundancy, the db might still contain incorrect info., centralized control of the db can help in avoiding such problems, insofar as they can be avoided by permitting the da to define, and the dba to implement.
· security can be avoided: dba under appropriate direction from the da can ensure that the only means of access to the db is though the proper channels, and hence can define security constraints to be checked whenever access is attempted to sensitive data.
· conflicting requirements can be balanced: knowing the overall requirements of the enterprise, the dba under the direction of da can structure the sys. as to provide an overall service that is the best for the enterprise.
· standards can be enforced: with central control of the db, the dba under the direction of da can ensure that the applicable standards are observed in the representation of the data.
why database
the advantages of using db sys:
· compactness: ther is no need for possibly voluminous of paper files.
· speed: the machine can retrieve and updating data far faster than a human can.
· less drudgery: much of the sheer tedium of maintaining files by hand is eliminated. mechanical tasks are always better done by machine.
· currency: accurate, up-to-date info. is available on demand at any time.
· protection: the data can be better protected against unintentional loss and unlawful access
data base management system (dbms): is a generalized s.w interface to translate between the global view of the data in the db and the local view expected by each application program.
the dbms facilities
1- the dbms can make the procedure for coping with the new data item simples and reliable.
2- changing the format of data item doesn t affecting the application program.
3- data could be transferred to different storage devices.
4- dbms provide data structuring facilities which are capable of expressing the often complex relationships which may exist between data item, moreover it must by possible to access data sufficiently quickly to satisfy the needs of a variety.
5- dbms should validate data before permitting it to be stored with db and should provide comprehensive recovery procedure.
6- an acceptable compromise sol. must be south which satisfy all user requirements while optimizing the over all performance of the dbs this task will be made easier if the dbms contains facilities both monitoring db performance and also for tuning the sys performance.
7- a dbms may allow access to the same db via different host language feature which facilitates the sharing of same db by different kinds of application.
host language:- an application program will need some of interfacing with db, one sol. is to extend an existing procedural by adding commands designed specifically for db manipulation which is called h.l.l.
data manipulation language (dml):- is the additional db commands.
schema:- is the data view which is specified in language which dbms s.w is designed to understand.
dbms should have for two types of schema is normally global schema and local schema. in fact, in this two level architecture, the global schema would need to contain two essentially different types of info.: there is info. about what data is available in the db, and info. about the way that data is stored and accessed. taken together with local schema there are three schemas, these are: conceptual, internal, and external schemas.
local schema local view pa,…..,pn
dbms
global schema global view db
what data ways of
should be stored data and internal schema
stored accessing data
conceptual schema
is the description of all the data interested to the enterprise which is to be stored in the db. it specifies the logical data content of the db and the constraints which apply to the data, for ex:
1- the age student must not exceed 40 years.
2- the data hold for student is st-no, name, address.
3- the age must be numeric.
internal schema
describe how the stored data is implemented at the level of stored records, stored records format, indexes, hashing algo, pointers, block size, storage media,…. etc.
external schema
describe the local view of db required by an application programs, if these applications requires identical local view they may share the same external schema.
properties of the data such as the format of data item may be specified by an external schema but it can t override any of the constrained imposed by the conceptual schema.
mapping
the dbms is responsible for mapping between the three types of schemas. it must be capable of checking the schema for consistency and must use the info. in the schema to map between external schema and internal schema via the conceptual schema as shown in the figure below:-
external/conceptual external/conceptual external/conceptual external/conceptual
mapping mapping mapping mapping
dbms components:- the dbms with three level architecture
include:
1. use langauge interface including (dml).
2. an extrernal schema data description language.
3. a conceptual schema data description language.
4. an internal schema data description language.
5. a data base central system(dbcs) which will access the db as shown in the figure below.
external/conceptual mapping
ss
|
external/conceptual mapping
ss
|
replies
ask
advantages of three level architecture
the three level architecture simplifies the design and management of adb sys. by providing higher level prog/data independence. some of the applications of three-level architecture with respond to prog/data independent include:-
1. change in conceptual schema: a change in conceptual schema will not affect any existing application program unless its external schema is uncompatible with the new conceptual schema.
2. change in internal schema: changing in pattrens of data usage may tuning the dbms (for ex. change block size, add new pointer between records). the seperation of extrernal schema from internal schema mean that the internal schema can be tunned without changing application program. also there is no danger that the change to internal schema will corrupt the conceptual schema.
3. change in external schema: definition of new extrernal schema or alterations to existing one will not affect the application program which use the extrernal schema.
data model:
is a blue print of how data is stored in db and is similar to an architecture approach for how data is stored a pretty picture. commonly known as an entry relationship diagram. data model can be loosely used to describe an organized and ordered set of info. stored on computer. this orderd set of data is often structured using a data modeling solution is such a way as to make the retrival of and change to that data more efficient. depending on the type of application using db, the db structure can be modified to allow for efficient changes to that data.
1- hierarchal db model: it is an inverted tree like structure. the tables of this model take an a child-parent relationship. each child table has a single parent table, and each parent table can have multiple child tables. child table are completely dependent on parent table, therefore, a child table can exist only if parent t able dose. it follows that any entries in child tables can only exist where corresponding parent entries exist in parent tables. the result of this structure is that the hierarchal db model supports one -to – many relationships, as shown in the figure below:
hierarchal db model
the disadvantages of this model are that any access must originate from the root. in this case, the figure of the company the search for an employee cann`t done without first finding the company department, the employee`s manager and finally the employee.
2- network db model: this model is essentially a refinement of hierarchal db model. network model allows child tables to have more than one parent, thus creating a network like table structure. multiple parent tables for each chiled allows for many – to – many relationships in addition to one -to – many relationships, as shown in the figure below:
network db model
3- relational db model: it improves on the restriction of hierarchical structure, not completely abandoning the hierarchical of data as in the figure below:-
relational db model
any table can be accessed directly without having to access all parent objects. the trick is to know what to look for, another benefit is that any tables can be linked together, regardless of their hierarchical structure therefore, a table can be linked to both any number of parent tables and any number of chiled tables.
the conceptual schema represented in this model as a relation or tables.
there are no. of restriction will be applied to table, these are:
1- the ordering of rows is not significant, that is, the row can be interchanged without affecting these info.
2- the ordering of columns is not significant.
3- each row/columns intersection contains a single attribute value, multiple values are not allowed.
4- each row in the table must be distinct, no two rows can have the same attribute values throughout.
|
part #
|
part-description
|
quality in stock
|
|
p2
|
nut
|
5000
|
|
p1
|
bolt
|
8500
|
|
p3
|
washer
|
2750
|
|
p4
|
nut
|
2326
|
the three most important operations in relational algebra are those for constructing a new table:-
1- by selecting rows from an existing table.
2- by selecting columns from an existing table.
3- by linking together tables on the basis of value in specified columns of each.
employee
|
emp#
|
emp-name
|
room#
|
|
e1
|
baker
|
r2
|
|
e4
|
smith
|
r4
|
|
e5
|
moss
|
r2
|
|
e8
|
smith
|
r3
|
|
e9
|
wells
|
r4
|
|
e7
|
smith
|
r6
|
room
|
room#
|
capacity
|
|
r1
|
5
|
|
r2
|
4
|
|
r3
|
1
|
|
r4
|
3
|
|
r5
|
4
|
|
r6
|
1
|
|
r7
|
2
|
phone
|
room#
|
extension#
|
|
r2
|
217
|
|
r2
|
218
|
|
r2
|
219
|
|
r3
|
350
|
|
r4
|
451
|
|
r4
|
454
|
|
r5
|
910
|
to creat a new table according to above rules:-
1- < result table name> = row of < table name> where condition
tr = row of employee where emp-name="smith"
tr
|
emp#
|
emp-name
|
room#
|
|
e4
|
smith
|
r4
|
|
e8
|
smith
|
r3
|
|
e7
|
smith
|
r6
|
ex:- design a table the capacity of its rooms are < 4 and room 7 out of the comparison
rs = row of room where capacity < 4 and room# != 7
rs
|
room#
|
capacity
|
|
r3
|
1
|
|
r4
|
3
|
|
r6
|
1
|
ex:- design a table the contain name = smith with room# = 4
ss = row of employee where emp-name="smith" and room# ="r 4"
|
emp#
|
emp-name
|
room#
|
|
e4
|
smith
|
r4
|
2- < result table name> = columns of < table name> under attribute list
tr = column of employee under room#
tr
duplicate is not allowed
ex:- design a table contain room# with emp-name
ss = column of employee under room# , emp-name
ss
|
room#
|
emp-name
|
|
r2
|
baker
|
|
r4
|
smith
|
|
r2
|
moss
|
|
r3
|
smith
|
|
r4
|
wells
|
|
r6
|
smith
|
3- linking tables
the join operation links tables together on the basis of specified columns in each. the syntax is :-
< result table name> = join of < table list> on < attribute list>
rt = join of employee, room on room#
|
emp#
|
emp-name
|
room#
|
room#
|
capacity
|
|
e1
|
baker
|
r2
|
r2
|
4
|
|
e4
|
smith
|
r4
|
r4
|
3
|
|
e5
|
moss
|
r2
|
r2
|
4
|
|
e8
|
smith
|
r3
|
r3
|
1
|
|
e9
|
wells
|
r4
|
r4
|
3
|
|
e7
|
smith
|
r6
|
r6
|
1
|
ex:- design a table from employee , phone depend on room#
as = join of employee ,phone on room#
|
emp#
|
emp-name
|
room#
|
room#
|
extension
|
|
e1
|
baker
|
r2
|
r2
|
217
|
|
e1
|
baker
|
r2
|
r2
|
218
|
|
e1
|
baker
|
r2
|
r2
|
219
|
|
e4
|
smith
|
r4
|
r4
|
451
|
|
e4
|
smith
|
r4
|
r4
|
454
|
|
e5
|
moss
|
r2
|
r2
|
217
|
|
e5
|
moss
|
r2
|
r2
|
218
|
|
e5
|
moss
|
r2
|
r2
|
219
|
|
e8
|
smith
|
r3
|
r3
|
350
|
|
e9
|
wells
|
r4
|
r4
|
451
|
|
e9
|
wells
|
r4
|
r4
|
454
|
normalization
removes duplication and minimizes redundant chunks of data. the result is better organization and more effective use of physical spaces among other factors.
normal forms
the precies acadmic definitions of normal forms (nf)
·1st normal form (1nf): eliminate repeating groups such that all record in all tables can be identified uniquely by primary key in each table, in other word all fields other than the primary key most depend on the primary key.
·2nd normal form (2nf): all non-key values must fully functionally depend on the primary key. no potential dependencies are allowed. a partial dependency exists when a field is fully dependent on a part of a composite primary key.
·3rd normal form (3nf): eliminate transitive dependencies, meaning that a field is indirectly determined by the primary key. this is because the field is functionally dependent on another field, where as the other field is depend on the primary key.
·boyce-codd normal form (bcnf): every determinant in a table is a candidate key. if there is only one candidate key. 3nf, bcnf are one and same.
the facilities of each form
· 1st normal form (1nf):-
v eliminate repeating group.
v defines primary key (p.k).
v all records must be identified uniquely with p.k., a p.k. is unique and thus no duplicate values are allowed.
v all fields other than the p.k. must depend on the p.k. either directly or indirectly.
v all fields must contain a single value.
v all values in each field must be of the same data type.
v creat a new table to move the repeating group from the original table.
· 2nd normal form (2nf):-
v the table must be in 1nf.
v all non-key values must be fully functionally dependent on the p.k. in other word, non-key fields not completely and individually dependent on the p.k. are not allowed.
v partial dependencies must be removed. a partial depend is a special type of functional dependency that exist when a field is fully dependent on a part of a composite p.k.
v creat a new table to separate the partially dependent part of the p.k. and its dependent fields.
· boyce-codd normal form (bcnf):-
v a table must be in 3nf.
v a table can have only one candidate key.
structured query language (sql)
intrthe relational data model as proposed by codd provided the basic concepts for a new database management system, the relational database management system (rdbms). soon after the relational model was defined, a number of relational database languages were developed and used for instructing the rdbms. structured query language being one of them.
the sql language is so inextricably tied to relational database theory that it is impossible to discuss it without also discussing the relational data model.
the structured query language is a relational database language. by itself, sql does not make a dbms. it is just a medium which is used to as a means of communicating to the dbms what you want it to do. sql commands consist of english like statements which are used to query, insert, updating and deleting data. what is the meaning of `english like , is that sql commands resemble english language sentences in their construction and use. this does not mean that you can type in something like "pull up the figures for last quarter s sales" and expect sql to understand your request. what it does mean is that sql is a lot easier to learn and understand than most of the other computer languages.
sql is sometimes referred to as a non-procedural database language. what this means is that when you issue an sql command to retrieve data from a database, you do not have to explicitly tell sql where to look for the data. it is enough just to tell sql what data you want to be retrieved. the dbms will take care of locating the information in the database. this is very useful because it means that users do not need to have any knowledge of where the data is and how to get at it. procedural languages such as cobol or pascal and even older databases based on the network and hierarchical data models require that users specify what data to retrieve and also how to get at it. most large corporate databases are held on several different computers in different parts of the building or even at different geographic locations. in such situations, the non-procedural nature of sql makes flexible, ad hoc querying and data retrieval possible. users can construct and execute an sql query, look at the data retrieved, and change the query if needed all in a spontaneous manner. to perform similar queries using a procedural language such as cobol would mean that you would have to create, compile and run one computer programs for each query.
commercial database management systems allow sql to be used in two distinct ways. first, sql commands can be typed at the command line directly. the dbms interprets and processes the sql commands immediately, and any result rows that are retrieved are displayed. this method of sql processing is called interactive sql. the second method is called programmatic sql. here, sql statements are embedded in a host language such as cobol or c. sql needs a host language because sql is not really a complete computer programming language as such. it has no statements or constructs that allow a program to branch or loop. the host language provides the necessary looping and branching structures and the interface with the user, while sql provides the statements to communicate with the dbms.
while the dbms maintains all the information in the database, applications can access this information through statements made in structured query language (sql), a language for specifying high level operations. these operations are called queries, and there are two types of queries: selection queries, which extract information from the database, and action queries, which updating the database. how the dbms maintains, updatings, and retrieves this information is something the application doesn’t have to deal with
why do we need queries
² natural languages (english) are too vague
? with complex questions, it can be hard to verify that the question was interpreted correctly, and that the answer we received is truly correct.
? consider the question: who are our best customers?
² we need a query system with more structure
² we need a standardized system so users and developers can learn one method that works on any (most) systems.
? query by example (qbe)
? sql
four questions to create a query
² what output do you want to see?
² what do you already know (or what constraints are given)?
² what tables are involved?
² how are the tables joined together?
المادة المعروضة اعلاه هي مدخل الى المحاضرة المرفوعة بواسطة استاذ(ة) المادة . وقد تبدو لك غير متكاملة . حيث يضع استاذ المادة في بعض الاحيان فقط الجزء الاول من المحاضرة من اجل الاطلاع على ما ستقوم بتحميله لاحقا . في نظام التعليم الالكتروني نوفر هذه الخدمة لكي نبقيك على اطلاع حول محتوى الملف الذي ستقوم بتحميله .