manas_design

8/7/2019 manas_design

http://slidepdf.com/reader/full/manasdesign 1/6

DESIGN ANALYSIS OF

UNIVERSITY DATABASE

Manas.H.R(28),Manohar Bhat(30), ’V’ sem ’A’ section

November 27, 2009

1 INTRODUCTION:As mentioned in the Software Requirements specification, we are supposed tocreate a university database on any platform in MySQL or ORACLE-9i or evenMS-SEQUEL SERVER. We are also supposed to make a user-friendly front-enddesktop environment, using PHP on MySQL or using VB.NET on ORACLE-9iand MS-SEQUEL SERVER. We should also see that, we maintain the databaseclassified, by authenticating users with passwords and suitable IDs. We alsoneed to maintain the SSL( Secured Socket Layered) communication by authen-ticating the users and maintaining a firewall by the root or the administratorto take a clean look over authorised and unauthorised users community.

2 PHASES IN DESIGNING THE DATABASE:2.1 ENTITY RELATIONSHIP MODELLING-

An entity-relationship (ER) diagram is a specialized graphic that illustrates theinterrelationships between entities in a database. ER diagrams often use sym-bols to represent three different types of information. Boxes are commonly usedto represent entities. Diamonds are normally used to represent relationshipsand ovals are used to represent attributes.

On seeing the details given by the client to create the database, we have re-searched out the following details:The entities and the attributes that we discovered were:

a)PROFESSOR- PROFESSORSSN,NAME,AGE,RANK,SPEC,HOURS,DNO.b)PROJECT- PROJECTNO,SPONSER,STARTDATE,ENDDATE,BUDJETMANAGERSSN,SUPERVISORSSN.

c)STUDENT- STUDENTSSN,NAME,AGE,DEGREE,DNO,PGCOURSE,PROJECTNO.d)DEPARTMENT- DEPARTMENTNO,NAME,CHAIRMAN,MAINOFFICE.e)CO-INVESTIGATOR- PROJECTNO,COINVESTIGATORSSN.f)STUDENT CO-ADVISOR- STUDENTSSN,STUDENTADVISORSSN.

1



We find two associative entities:-(Entity + Relationship + Attributes)a)Co-investigator.

b)Student Co-adviser.

Here both have the attributes including relationships.We have also found out cardinality ratios between relationships:a) Professor supervises Student (1,M : 1,N).b) Professor co-investigates Project (1 : N).c) Student workOn Project(1,M : 1,N).d) Student belongTo Department. (1 : 1).e) Student advises Student.(1,M : 1,N).

A clear view of the Entity-Relationship Diagram is shown in (figure-1).

2.2 LOGICAL ANALYSIS OF THE E-R DIAGRAM:

Entities and Attributes with respect to relationships are brought into tables.The content indexes are as shown below:a) PROFESSOR- PSSN,NAME,AGE,RANK,SPEC,HOURS,DNO.b) PROJECT- PNO,SPONSER,STARTDATE,ENDDATE,BUDJET,MSSN,SUSSN.c) STUDENT- SSN,NAME,AGE,DEGREE,DNO,PGCOURSE,PNO.d) DEPARTMENT- DNO,NAME,CHAIRMAN,MAINOFFICE.

Associative entities and cardinality are seperated into new tables. The con-tent indexes are shown below:

e) CO-INVESTIGATOR- PNO,COSSN.

f) STUDENT CO-ADVISOR- SSN,SASSN.

So, the relational table or view of the database inferring from the E-R dia-gram, is shown in the (figure-2).

2.3 NORMALISATION OF THE DATABASE TABLES:-

In the field of relational database design, normalization is a systematic wayof ensuring that a database structure is suitable for general-purpose queryingand free of certain undesirable characteristics insertion, update, and deletionanomalies that could lead to a loss of data integrity.E.F. Codd, the inventor of the relational model, introduced the concept of normalization and what we nowknow as the First Normal Form (1NF) in 1970. Codd went on to define the Sec-ond Normal Form (2NF) and Third Normal Form (3NF) in 1971,and Codd and

2



Raymond F. Boyce defined the Boyce-Codd Normal Form in 1974.Higher nor-

mal forms were defined by other theorists in subsequent years, the most recentbeing the Sixth Normal Form (6NF) introduced by Chris Date, Hugh Darwen,and Nikos Lorentzos in 2002.Informally, a relational database table (the com-puterized representation of a relation) is often described as ”normalized” if it isin the Third Normal Form.Most 3NF tables are free of insertion, update, anddeletion anomalies, i.e. in most cases 3NF tables adhere to BCNF, 4NF, and5NF (but typically not 6NF).A standard piece of database design guidance isthat the designer should create a fully normalized dsign; selective de normal-ization can subsequently be performed for performance reasons.However, somemodeling disciplines, such as the dimensional modelling approach to data ware-house design, explicitly recommend non-normalized designs, i.e. designs that inlarge part do not adhere to 3NF.

Background to normalization:a) Functional dependency: Attribute B has a functional dependency on at-tribute A (i.e., A to B) if, for each value of attribute A, there is exactly onevalue of attribute B. If value of A is repeating in tuples then value of B willalso repeat. In our example, Employee Address has a functional dependencyon Employee ID, because a particular Employee ID value corresponds to oneand only one Employee Address value. (Note that the reverse need not be true:several employees could live at the same address and therefore one EmployeeAddress value could correspond to more than one Employee ID. Employee IDis therefore not functionally dependent on Employee Address.) An attributemay be functionally dependent either on a single attribute or on a combinationof attributes. It is not possible to determine the extent to which a design is

normalized without understanding what functional dependencies apply to theattributes within its tables; understanding this, in turn, requires knowledge of the problem domain. For example, an Employer may require certain employeesto split their time between two locations, such as New York City and Lon-don, and therefore want to allow Employees to have more than one EmployeeAddress. In this case, Employee Address would no longer be functionally de-pendent on Employee ID.

b) Another way to look at the above is by reviewing basic mathe-

matical functions:

Let F(x) be a mathematical function of one independent variable. The inde-pendent variable is analogous to the attribute A. The dependent variable (orthe dependent attribute using the terminology above), and hence the term func-

tional dependency, is the value of F(A); A is an independent attribute. As weknow, mathematical functions can have only one output. Notationally speaking,it is common to express this relationship in mathematics as F(A) = B; or, F :A to B.

There are also functions of more than one independent variablecommonly, thisis referred to as multivariable functions. This idea represents an attribute being

3



functionally dependent on a combination of attributes. Hence, F(x,y,z) contains

three independent variables, or independent attributes, and one dependent at-tribute, namely, F(x,y,z). In multivariable functions, there can only be oneoutput, or one dependent variable, or attribute.

c) Trivial functional dependency:

A trivial functional dependency is a functional dependency of an attribute on asuperset of itself. Employee ID, Employee Address Employee Address is trivial,as is Employee Address Employee Address.Full functional dependencyAn attribute is fully functionally dependent on a set of attributes X if it is func-tionally dependent on X, and not functionally dependent on any proper subsetof X. Employee Address has a functional dependency on Employee ID, Skill,but not a full functional dependency, because it is also dependent on Employee

ID.

d) Transitive dependency:

A transitive dependency is an indirect functional dependency, one in which Xto Z only by virtue of X to Y and Y to Z.e) Multivalued dependency:

A multivalued dependency is a constraint according to which the presence of certain rows in a table implies the presence of certain other rows.f) Join dependency:

A table T is subject to a join dependency if T can always be recreated by joiningmultiple tables each having a subset of the attributes of T.g) Super key:

A super key is an attribute or set of attributes that uniquely identifies rowswithin a table; in other words, two distinct rows are always guaranteed to havedistinct super keys. Employee ID, Employee Address, Skill would be a superkey for the ”Employees’ Skills” table; Employee ID, Skill would also be a superkey.h) Candidate key:

A candidate key is a minimal super key, that is, a super key for which we cansay that no proper subset of it is also a super key. Employee Id, Skill would bea candidate key for the ”Employees’ Skills” table.i) Non-prime attribute:

A non-prime attribute is an attribute that does not occur in any candidate key.Employee Address would be a non-prime attribute in the ”Employees’ Skills”table.

j) Primary key:Most DBMS’ require a table to be defined as having a single unique key, ratherthan a number of possible unique keys. A primary key is a key which thedatabase designer has designated for this purpose.

We have used four basic normal forms for the normalisation:-

4



a) First Normal Form:

First normal form (1NF or Minimal Form) is a normal form used in databasenormalization. A relational database table that adheres to 1NF is one thatmeets a certain minimum set of criteria. These criteria are basically concernedwith ensuring that the table is a faithful representation of a relation and thatit is free of repeating groups.

b) Second Normal Form:

Second normal form (2NF) is a normal form used in database normalization.2NF was originally defined by E.F. Codd in 1971. A table that is in first normalform (1NF) must meet additional criteria if it is to qualify for second normalform. Specifically: a 1NF table is in 2NF if and only if, given any candidate keyand any attribute that is not a constituent of a candidate key, the non-key at-tribute depends upon the whole of the candidate key rather than just a part of it.

c) Third Normal Form:

The third normal form (3NF) is a normal form used in database normalization.3NF was originally defined by E.F. Codd[1] in 1971. Codd’s definition statesthat a table is in 3NF if and only if both of the following conditions hold:

i) The relation R (table) is in second normal form (2NF).ii) Every non-prime attribute of R is non-transitively dependent (i.e. di-

rectly dependent) on every key of R.

d) Boyce-Codd Normal Form:

Boyce-Codd normal form (or BCNF) is a normal form used in database nor-

malization. It is a slightly stronger version of the third normal form (3NF). Atable is in Boyce-Codd normal form if and only if, for every one of its non-trivialfunctional dependencies X to Y, X is a super key that is, X is either a candidatekey or a superset thereof.

After applying the normalisation, considering to remove all constraints we getfour more extra entities, two produced during the 1NF and two during 2NF.

They run as follows:-a) PROFESSOR- PSSN,NAME,AGE,RANK,SPEC.b) PROJECT- PNO,SPONSER,STARTDATE,ENDDATE,BUDJET,MSSN,SUSSN.c) STUDENT- SSN,NAME,AGE,DEGREE,DNO,PGCOURSE.

d) DEPARTMENT- DNO,NAME,CHAIRMAN,MAINOFFICE.e) CO INVESTIGATOR- PNO,COSSN.f) STUDENT ADVISOR- SSN,SASSN.g) PROJECT RECORD- SSN,PNO.h) DEPT DETAILS- PSSN,DNO,TIME.

So, the relational table or view of the database after Normalisation pertain-

5



ing to the E-R diagram, is shown in the (figure-3).

3 DATA STRUCTURES-

The datastructures usually used in manufacturing DBMS are hash tables, linkedlists and trees. So they can be called primitive datastructures. These arethe Datastructures usedindirectly when we switch on an MySQL server or elsewhere.

4 PERFORMANCE-

DBMS does not particularly have any packages with respect to performance.The whole thing itself is designed super-beneficially and user friendly for highperformance purpose. This is developed by an object oriented mixture of JAVAand C++.

5 INPUT AND OUTPUT DESIGN-

Input is given in the front-end and the output is processed from back-end likemySQL or VB.NET

6 REFERENCES-

1) Software Requirements Specification.2) www.google.co.in.3) www.wikipedia.org.4) Fundamentals of Database Management Systems by Elmasiri and Navathe.

6

manas_design

Documents