database design concept

Topics: Entity-relationship model, Database normalization, Database management system Pages: 15 (2579 words) Published: January 17, 2014
Unit 1 Database Design Concept

Table of Contents
Entity Relationship Diagram1
Entities and Attributes1
Relationships2
Artist Database ERD and Tables3
General Rules Governing Relationships Among Tables3
Normalization Rules7
The Need for Normalization7
Conversion to 1NF7
Conversion to 2NF8
Conversion to 3NF10
Boyce-Codd Normal Form (BCNF)10
Database Design11
Changing Data into Information11
The Information System11
Systems Development Life Cycle (SDLC)13
Database Lifecycle (DBLC)14
Phase 1: Database Initial Study15
Phase 2: Database Design15
Phase 3: Implementation and Loading17
Phase 4: Testing and Evaluation18
Phase 5: Operation18
Phase 6: Maintenance and Evaluation18
DB Design Strategy Notes19
Centralized vs. Decentralized Design19
Database Management System19
Oracle 9i download site19
Install and Uninstall Oracle Database Server19

Entity Relationship Diagram
Represents the conceptual database as viewed by the end user.

Entities and Attributes
Entity –
Corresponds to entire table, not to a row in the relational environment. Represented by rectangle containing the entity name, which is a noun written in capital letters. Attributes –
Characteristics of entities – the STUDENT entity could include the attributes STU_LNAME, STU_FNAME and STU_INITIAL. Domain – domain is set of possible values
For the numeric attribute GPA is written (0,4) because the lowest and highest possible GPA is 0 and 4, respectively. For the character attribute SEX consists of only two possibilities, M or F. For the date attribute HIRE_DATE consists of all dates from startup date to current date. Primary Keys – primary keys are underlined in the E-R diagram. Format: TABLE NAME (KEY ATTRIBUTE 1, ATTRIBUTE 2, …)

For example, the CLASS_CODE is a primary key, then
CLASS (CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM, PROF_NUM) If the CLASS_CODE is deleted, then may be represented by
CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM, PROF_NUM) Simple Attribute – Cannot be subdivided; ex: age, sex, marital status would be classified as simple attributes. Composite Attribute – Can be subdivided into additional attributes; ex: address into street, city, zip; Phone number into area code and exchange number. Single-valued Attribute – Can have only a single value; ex: person has one social security number. Multi-valued Attribute – Can have many values; ex: person may have several college degrees or householder may own more phone numbers. Derived Attribute – Can be derived with algorithm, instead of being physically stored within the database; ex: age can be derived from date of birth – by the integer value of difference between the current date and the employee’s date of birth.

Relationships
A relationship is an association between entities. For example, a STUDENT takes a CLASS, a PROFESSOR teaches a CLASS, a DEPARTMENT employs a PROFESSOR, a DIVISION is managed by an EMPLOYEE, and AIRCRAFT is flown by a CREW, etc. The entities that participate in a relationship, i.e., connected entities, are called participants. Connectivity and Cardinality

Connectivity describes relationship classification. It may be classified as 1:1, 1:M, M:N. Cardinality expresses the specific number of entity occurrences associated with one occurrence of related entity. Connectivity and cardinality are established by very concise statements known as business rules, which is derived from a precise and detailed description of an organization’s data environment, also establish the E-R model’s entities, attributes, relationships, connectivities and constraints. Relationship Strength

Existence Dependence – Entity’s existence depends on existence of related entities Weak (non-identifying) Relationships
PK of related entity doesn’t contain PK component of parent entity For example, the definition of the COURSE and CLASS entities is COURSE(CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT)...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Data Modeling Concepts Essay
  • Database Design Essay
  • Essay on Comparative analysis of database design
  • CIS Database Notes Essay
  • Essay about Dad (Data Analysis and Design)
  • Why Good Database Design Is Important in Performing Data Operation? Essay
  • Logical Design Essay
  • Database Design and Implementation Essay

Become a StudyMode Member

Sign Up - It's Free