DATABASE DESIGN CONCEPTS

NAME : NALIN DASSANAYAKE STUDENT ID : 105311 SEPTEMBER 2009

TABLE OF CONTENT

 Introduction.............................................................................................2
A Database.....................................................................................................................2 Importance of Database................................................................................................3 Data modeeling..............................................................................................................3 Entity.............................................................................................................................5 Attribute........................................................................................................................6 Relationship...................................................................................................................6

 Scenario....................................................................................................7  Analysis and Design................................................................................ 7

Mapping the Entity Relationship Diagram....................................................................7 The ER Diagram for the given scenario........................................................................9 Normalization...............................................................................................................10 Tables Creating............................................................................................................13 Insert Values.................................................................................................................16

 Test data and annotated results............................................................17  Conclusion and Comments  References

INTRODUCTION
Database Management System A Database Management (DBMS) is software designed to assist in maintaining and utilizing large collection of data, and a need for such systems, as well as their use, is growing rapidly.Database Management System allows organizations to place control of organizationwide database development in the hands of database administrators and other specialists. It
allows organizations to place control of organization-wide database development in the hands of database administrators and other specialists. Database models for instants, relational models or network models are used in database management system to improve the file storage functions. By doing so, it helps to improve Data redundancy Retrieving data Data isolation Data accessing Security issues

A Database Database is a collection of data of a particular enterprise. It contain the data which related to that particular enterprise such as if it is a university, it contain student ID, grades, etc. There are different types of data models. Several models are Hierarchical model, Network models, Relational models, Entity-Relationship data models, Object-based data models, Semi-structured data model. Hierarchical models and network models are some of the older database models which not in use today. Object-based data models and semi-structured data models are the models which use today. Semi-structured data models such as XML (Extensible Mark-up Language) are defined by WWW Consortium. This is originally intended as a document mark-up language. It has the ability to specify new tags and to create nested tag structures. And verities of tools are available parsing, browsing and querying XML documents. Object-Relational data models allow attributes to have complex types, including non-atomic values such as nested relations. It also preserve relational foundations in particular the declarative access to data while extending the data modeling power. Entity Relationship data models has existed for more than thirty (30) years. In ER data modeling, it is easier to explain and discuss about the database. Entity relationship is based in two things. Entities Attributes

Entities can be objects with physical existence or conceptual existence. And attributes describes the entities.

Importance of Database

A data Base has lots of advantages over the normal manual methods for storing data because of the following : Compactness Speed is high because the computer can restore the stored Data Base and upgrade it very faster than manually doing it. Less drudgery Currency It can gives a central control system for a project for its operating data.

because of the central control system The redundancy can be reduced. The inconsistency can be avoided. The data can be shared Security restriction can be applied

Data modelling
Data model is a set of constructs using to arrange data. Data modelling is a method used to define and analyse data requirements needed to support the business processes of an organization. There are several types of data models which used in the past and modern concept and models of constructing a database. Hierarchical Model Network model Relational model Object/Relational Model Object Oriented Model Semi-structured Model

Hierarchical Model A hierarchical data model is a data model in which the data is organized into a tree-like structure. The structure allows repeating information using parent/child relationships. A parent can have many children but a child can have only one parent. In a database, an entity type is the equivalent of a table; each individual record is represented as a row and an attribute as a column. This model, which was implemented primarily by IBM's Information Management System (IMS) only allows one-to-one or one-to-many relationships between entities. Network Model Unlike the hierarchical model, the network model allows each record to have multiple parent and child records, forming a lattice structure. Some data were more naturally modelled with more than one parent per child. So, the network model permitted the modelling of many-tomany relationships in data. The basic data modelling construct in the network model is the set construct. A set consists of an owner record type, a set name, and a member record type. A member record type can have that role in more than one set, hence the multi-parent concept is supported. An owner record type can also be a member or owner in another set. The data model is a simple network, and link and intersection record types (called junction records by IDMS) may exist, as well as sets between them. The complete network of relationships is represented by several pair-wise sets; in each set some record type is owner and one or more record types are members. Relational model The Relational Model was the first theoretically founded and well thought out Data Model, proposed by EfCodd in 1970. It has been the foundation of most database software and theoretical database research ever since. In this model data is organize as a table which holds unique row to identify the records.

Hold atomic values. Holds unique rows and same data type columns. Holds a unique name. Based on relational algebra. Easy to execute Ability to join multiple tables on multiple fields.

Object-Relational Model Object-relational model is a database management system similar to a relational database, but with an object-oriented database model; objects, classes and inheritance are directly supported in database schemas and in the query language.

Object-Oriented Model This model will define the DBMS and its relation to the Object oriented programming languages. In contrast to a relational DBMS where a complex data structure must be flattened out to fit into tables or joined together from those tables to form the in-memory structure, object DBMSs have no performance overhead to store or retrieve a web or hierarchy of interrelated objects. This one-to-one mapping of object programming language objects to database objects has two benefits over other storage approaches: it provides higher performance management of objects, and it enables better management of the complex interrelationships between objects. This makes object DBMSs better suited to support applications.

Semi-structured Model In semi-structured data model, the information that is normally associated with a schema is contained within the data. Therefore in semi-structured model there is no clear separation between the data and schema.

Entity-Relationship Model (ERM) An Entity-Relationship Model is an abstract and conceptual representation of data. Entityrelationship modelling is a database modelling method, used to produce a type of conceptual schema or semantic data model of a system, often a relational database, and its requirements in a top-down fashion.

Entity
An entity may be defined as a thing which is recognized as being capable of an independent existence and which can be uniquely identified. An entity is an abstraction from the complexities of some domain. When we speak of an entity we normally speak of some aspect of the real world which can be distinguished from other aspects of the real world. An entity may be a physical object such as a house or a car, an event such as a house sale or a car service, or a concept such as a customer transaction or order. Although the term entity is the one most commonly used, following When we should really distinguish between an entity and an entity-type. An entity-type is a category. An entity, strictly speaking, is an instance of a given entity-type. There are usually many instances of an entity-type. Because the term entity-type is somewhat cumbersome, most people tend to use the term entity as a synonym for this term. Entities can be thought of as nouns. Examples: a computer, an employee, a song, a mathematical theorem. Entities are represented as rectangles.

Attributes
Attribute defines as a particular property of an entity which represents behaviour of the entity. Depending on the nature of the attribute, there are three different kinds of attributes.

Composite attributesComposite attribute going through with the definition, composite attribute holds multiple components. As simple attribute holds only atomic values in here can be atomic or composite Multi value attributesMulti value attributes depending on the number of values that holds in an attributes this divide perform and multi-value attributes represent the more than single value for a particular entity. Derived attributesDerived attributes refer as an attribute which can obtained from another attribute or related entities. Below figure will show the way to represent in an ER diagram. In their radius will determine from the circumference attribute.

Relationship
Relationships explain how two entities share details in the database structure. And also shows how two or more entities are related to one another. Relationships can be thought of as verbs, linking two or more nouns. As an example, An owns relationship between a company and a computer A supervises relationship between an employee and department A performs relationship between an singer and a song There are four different kind of relationships types between the entitiesOne to One One to Many Many to One Many to Many In ER diagrams relationships are represented as diamonds, connected by two lines to each of the entities in the relationship.

Employee

Supervise

department

Scenario
Retail shop Fashion4u is a large business in London. So it is not easy them to keep their data in order to date. By maintaining manual file system it is hard to update data. So it will be easier to maintain them by computerizing the data. And also itll be secured than maintaining manual file systems. At the shop in a same time many workers want to maintain order details. A database can be used to render a view of database to many uses concurrently.

Analysis and Design

Mapping the Entity Relationship Diagram An Entity-Relationship diagram can be mapped, or translated, into a schema, or plan, for a relational database by following the series of steps, on the following pages. The steps should be followed in order to generate the best quality schema. The resulting schema may be refined to improve the ease with which it can be implemented.

Symbols that use to identify the diagrams properties

The ER Diagram to the given scenario

Sttreet Sto Midl_nam Last_nam Cid CTName First_name Name Stid Phone_No Category Staff Pn_mobil ISA F_Shoe Is_ in Belt Icd
Idesc

Stpostcode

Address Pn_home

Size 0 .. * 1 .. * SBag Place HBag Colour 1 .. * Length 1 .. *

Material

Oder_no

Price Qnt_in_st Re_od_lvl Re_od_qnt

Item

*.. 1

Contain

*.. 1

Order

Oder_Qnt

1 .. *

1 .. *

Total

Supp ly_by 1 .. * No Supplier 1 .. *

Fullfill _by

Street

SPAddress P_office Postcode Name Spid Phone P_mobil

Normalize

1NF 2NF 3NF

Item (Icd, Idesc, Price, Qnt_in_st, Re_od_lvl, Re_od_qnt) Item (Icd, Idesc, Price, Qnt_in_st, Re_od_lvl, Re_od_Qnt) Item (Icd, Idesc, Price, Qnt_in_st, Re_od_lvl, Re_od_qnt)

1NF 2NF 3NF

Category (Cid, CTName, Icd) Category (Cid, CTname, Icd) Category (Cid, CTName, Icd)

1NF 2NF 3NF

F_Shoes (Cid, Size) F_Shoes (Cid, Size) F_Shoes (Cid, Size)

1NF 2NF 3NF

Belt (Cid, Length ) Belt (Cid, Length ) Belt (Cid, Length )

1NF 2NF 3NF

H_Bags (Cid, Material) H_Bags (Cid, Material) H_Bags (Cid, Material)

1NF 2NF 3NF

S_Bags (Cid, Colour) S_Bags (Cid, Colour) S_Bags (Cid, Colour)

1NF 2NF 3NF

Order_Item (Icd, Order_no, Oreder_date) Order-Item ( Icd, Order_no, Order_date) Order_Item (Icd, Order_no, Order_Date)

1NF 2NF 3NF

Order_Details (Order_No, Total, Order_Qnt, Stid) Order_Details (Order_No, Total, Order_Qnt, Stid) Order_Details (Order_No, Total, Order_Qnt, Stid)

1NF 2NF 3NF

Supplier (Spid, Name, No, Street, Postcode) Supplier (Spid, Name, No, Street, Postcode) Supplier (Spid, Name, No, Street, Postcode) Supplier_Address (Spid, No, Street, Postcode)

1NF 2NF 3NF

Supplier Phone (Spid, Pmobile, POffice) Supplier Phone (Spid, Pmobile, POffice) Supplier Phone (Spid, Pmobile, POffice)

1NF 2NF 3NF

Item_Sup (Icd, Spid) Item_Sup (Icd, Spid) Item_Sup (Icd, Spid)

1NF 2NF 3NF

Staff (Stid, First_name, Middle_name, Last_name, Stno, StStreet, Stpostcode) Staff (Stid, First_name, Middle_name, Last_name, Stno, StStreet, Stpostcode) Staff (Stid, First_name, Middle_name, Last_name, Stno, StStreet, Stpostcode)

1NF 2NF 3NF

Staff_Phone_No (Stid, Pn_home, Pn_mobile) Staff_Phone_No (Stid, Pn_home, Pn_mobile) Staff_Phone_No (Stid, Pn_home, Pn_mobile)

Creating Tables

create table Item( Icd varchar(10)not null primary key, Idesc varchar(100)not null, Price float(8)not null, Qnt_in_st int(5), Re_od_lvl int(10), Re_od_qnt int(10) ); Create table Category( Cid varchar(10)not null primary key, Icd varchar(10)not null, CTName varchar(15), Foreign Key(Icd) references Item(Icd) ); create table F_Shoes( Cid varchar(10)not null primary key, Size int(5), Foreign Key (Cid) references Category(Cid) ); create table Belts( Cid varchar(10)not null primary key, Length varchar(15), Foreign Key (Cid) references Category(Cid) ); create table H_Bags( Cid varchar(10)not null primary key, Material float(10), Foreign Key (Cid) references Category(Cid) ); create table S_Bags( Cid varchar(10)not null primary key, Colour int(10), Foreign Key (Cid) references Category(Cid) ); create table Staff( Stid varchar(10)not null primary key, First_name varchar(50)not null, Middle_name varchar(50), Last_name varchar(50)not null, Stno int(6)not null,

Ststreet varchar (30)not null, Stpostcode varchar(10)not null ); Create table Staff_Phone_No( Stid varchar(10)not null primary key, Pn_mobile char(15), Pn_home char(15), Foreign Key(Stid) references Supplier(Stid) );

create table Order_Details( Order_No varchar(10)not null primary key, Total float(15), Order_Qnt int(15), Stid varchar(10)not null, Foreign Key(Stid) references Staff(Stid) ); create table Order_Item( Icd varchar(10)not null, Order_No varchar(10)not null, Order_Date date, Foreign Key(Icd) references Item(Icd), Foreign Key(Order_No) references Order_Details(Order_No) ); create table Supplier( Spid varchar(10)not null primary key, Name varchar(50), No varchar(10), Street varchar(15), Postcode varchar(10)not null ); create table Item_Supplier( Spid varchar(10)not null, Icd varchar(10)not null, Foreign Key(Spid) references Supplier(Spid), Foreign Key(Icd) references Item(Icd) ); create table Supplier_Phone( Spid varchar(10)not null, Pmobile char(15), Pofice char(15), Foreign Key(Spid) references Supplier(Spid) );

create table Address( No varchar(10)not null, Street varchar(15), Postcode varchar(20)not null, primary key(No,Postcode) );

Insert Values
insert into Item values('001A','WDShoe',150.30,250,100,200); insert into Item values('002A','GU Bag',150.36,120,200,50); insert into Item values('003A','SP Bag',80.25,90,400,250); insert into Item values('004A','ERD Shoe',120.40,15,60,55); insert into Item values('005A','PO Bags',140.99,160,150,30); insert into Item values('006A','GK Belts,99.99,150,100,220);

insert into Category values('B001','001A','Shoes'); insert into Category values('B002','002A','Bags'); insert into Category values('B003','003A','Bags'); insert into Category values('B004','004A','Shoe'); insert into Category values('B005','005A','Bags'); insert into Category values('B006','006A','Belts');

insert into F_Shoes values('B001',8); insert into F_Shoes values('B004',9); *insert into Belts values('B006',42); insert into H_Bags values('B005','Leather'); *insert into S_Bags values('B003',Black);

*insert into Staff values('ST1','Din',Sha,Frank,14,North end,S2 5UJ); insert into Staff values('ST2','Blake',D,Smith,25,Love lane,S8 5MK); insert into Staff values('ST3','Dan',Nick,Frank,52,Hill way,N3 3QT); insert into Staff values('ST4','Sonia',Sri,Raz,24,South west road,W4 4DH); insert into Staff values('ST5','Jim',Jane,Kim,15,Church way,N7 7HY);

insert into Staff_Phone_No values('ST1',07873849412,08044557562); insert into Staff_Phone_No values('ST2',07841583412,02065987562); insert into Staff_Phone_No values('ST3',07515844589,08065941845);

insert into Staff_Phone_No values('ST4',07541485232,02025456654); insert into Staff_Phone_No values('ST5',07581533412,08048952321);

insert into Order_Details values('O001',15.00,250,'ST1'); insert into Order_Details values('O002',25.00,100,'ST2'); insert into Order_Details values('O003',40.00,25,'ST3'); insert into Order_Details values('O004',65.50,11,'ST4'); insert into Order_Details values('O005',64.00,990,'ST5');

*insert into Order_Item values('A001','001A',2009-11-08'); insert into Order_Item values('B002','002A',2009-12-08'); insert into Order_Item values('C003','003A',2009-11-28'); insert into Order_Item values('D004','004A',2009-12-18'); insert into Order_Item values('E005','005A',2009-11-18');

*insert into Supplier values('SP1',IPC,50,Don Street,N4 4TH); insert into Supplier values('SP2',IDAC,'15,Mill Hill Street,N3 3TH); insert into Supplier values('SP3',KIT,'01,Wet Street,S5 5DF); insert into Supplier values('SP4',NOK,'25,Addison Street,S7 7BK); insert into Supplier values('SP5','LEE,High Street,N8 8UK);

insert into Item_Supplier values('SP1','001A'); insert into Item_Supplier values('SP2','002A'); insert into Item_Supplier values('SP3','003A'); insert into Item_Supplier values('SP4','004A'); insert into Item_Supplier values('SP5','005A');

insert into Supplier_Phone values('SP1',07585693412,08065987562); insert into Supplier_Phone values('SP2',07896325648,08024568962); insert into Supplier_Phone values('SP3',07954123689,02014563289); insert into Supplier_Phone values('SP4',07954896325,08032564789); insert into Supplier_Phone values('SP5',07599654223,02014569832);

insert into Address values('SP1,55.Don Street,N4 4TH); insert into Address values('SP2', Mill Hill Street,N3 3TH); insert into Address values('SP3', '01,Wet Street,S5 5DF); insert into Address values('SP4', '25,Addison Street,S7 7BK); insert into Address values('SP5', High Street,N8 8UK);

Test data and annotated results

List all of the Items in stock

SELECT Icd, Idesc FROM Item

List all of the Items available in each category SELECT CTName as Categoty , Idesc as ItemName FROM Item i, Category c WHERE i.Icd = c.Icd ORDER BY CTName;

List all of the Items not in stock SELECT Icd , Idesc as ItemName FROM Item WHERE Qnt_in_st = 0;

List all of the Items not in stock in each category of item SELECT CTName as Category , Idesc as ItemName FROM Item i , Category c WHERE i.Icd = c.Icd AND i. Qnt_in_st = 0;

List all of the Suppliers who supply a particular item SELECT CONTCAT(Name) Name FROM Supplier s , Item_Sup its , Item i WHERE s.Spid = its.Spid AND its.Icd = i.Icd AND i.Idesc = 'NK Bag';

List all of the Suppliers from a particular city SELECT CONCAT(Name) Name FROM Supplier s WHERE s.Postcode = ' N4 4YH';

List all of the transactions made between a particular periods SELECT od.Order_No , od.Total , od. Order_Qnt From Order_Details od , WHERE od.Order_No = Order_No AND OrderDate >= 2009-12-24 AND OrderDate <= 2009-12-28;

List all of the transactions made by a particular staff SELECT Order_No as Transaction_Number , Total , Order_Qnt FROM Staff s , Order_Details od WHERE s.Stid = od.Stid AND s.Stid = 'ST002';

List all the order generated during a particular period

SELECT Order_No FROM Order_Item WHERE Order_Date >= '2009-11-08' AND Order_Date <= 2009-12-28;

Conclusion and Comments

References