Data Modeling PDF

Chapter 7 Data Modeling I am a model. I am Data @Dr Muhammad Nuruddin Sudin  Introduction Represent data using entity relationship diagram (ERD) CRUD (create, read, update & delete) widely used in a relational database  E...

Chapter 7 Data Modeling I am a model. I am Data @Dr Muhammad Nuruddin Sudin  Introduction Represent data using entity relationship diagram (ERD) CRUD (create, read, update & delete) widely used in a relational database  Entity Relationship Diagram (ERD) Student Name Entity An object or component of data Attributes Characteristic of entity/ relationship Relationships association between 2 or more entity  Entity Entity Student Represent in rectangle shape Independent (complete by itself, not depent on other entity Has a primary key Weak Entity Loan Represent in double rectangle Dependent to other strong entity Don't has a primary key  Attributes Characteristic of entity Represent in ellipses/oval shape Student Name Attributes types Key Attribute Multivalued Attribute Derived Attribute Composite Attribute  Attributes Key Attribute uniquely identify an individual reccords (data pointer) Primary key - identify mechanism for each set table (underline) Foreign key (FK) - reference to primary key ini other table (can be use in other table, dash underline) Student Name Course StudentID CourseID StudentID C_name  Attributes Multivalued Attribute relation with FK, hold many value PhoneNo Student Email Derived Attribute value was derived from another attribute BornDate Student Age  Attributes Composite Attribute combination of attributes Student Fullname Address FirstName Street Postcode LastName City  In Summary Student Entity Weak Entity Loan Attribute Key Attribute StudentID Primary Key (PK) Foreign Key (FK) StudentID Multivalued Attribute PhoneNo Derived Attribute Age Composite Attribute Fullname LastName FirstName  Relationships association between 2 or more entity Cardinality possible number of occurrences Type of relationship one - one relationship one - many relationship many - one relationship many - many relationship  One - one 1 2 A B relationship 3 C one element of entity link to another one element of entity Student Transport (1 : 1) Student only can bring one transport in USIM An employee can get salary once per month Every nation can only have one capital Every traveller only have one passport  One - many 1 2 A B relationship 3 C one element of entity link to another more than one element of entity Student Subject (1 : N) Student can enrolls many subjects in USIM A customer can make various order Lecturer can teach many students Doctor can have many patient in a day  Many - one 1 2 A B relationship 3 C Many elements of entity link to another one element of entity Student Group (N : 1) Many student can assign a group for FYP These cats are belong to a person Items checked out in a single purchased Players in a football team  Many - many 1 2 A B relationship 3 C Many elements of entity link to another more than one element of entity Student Assigment (M : N) Students can have many assignments in a month A books can have many authors, An author can write many books Workers who doing multiple jobs Customer open account in multiple banks  Side Notes Some other relations related  Relational Database Schema Conceptual ERD need to be translated into a relational schema database system How to write syntax: CREATE TABLE Table_name (list of Attribute); 1- Create table name for entity 2- Add Primary Key as first attributes 3- Fill in the other attribute 4- Add Foreign Key (if available)  Relational Database Schema StudentID Name Student Age Email PhoneNum Relational schema syntax: CREATE TABLE Student ( StudentID, Name, Age, Email, PhoneNum );  Relational Database Schema One-one relationship StudentID Name TransportID Age Student Transport PhoneNum TransportID Type Email Relational schema syntax: CREATE TABLE Student ( StudentID, Name, Age, Email, PhoneNum, TransportID ); CREATE TABLE Transport ( TransportID, Type );  Relational Database Schema One-many relationship StudentID Name SubjectID Subject_name Age Student Subject Subject_time PhoneNum Email StudentID Relational schema syntax: CREATE TABLE Student ( StudentID, Name, Age, Email, PhoneNum ); CREATE TABLE Subject ( SubjectID, Subject_name, Subject_time, StudentID );  Relational Database Schema Many-one relationship StudentID Name GroupID Age Student Assign Group Group_name Tittle PhoneNum Email Relational schema syntax: CREATE TABLE Student ( StudentID, Name, Age, Email, PhoneNum ); CREATE TABLE Group ( GroupID, Group_name, Tittle ); CREATE TABLE Assign ( StudentID, GroupID );  Relational Database Schema Many-many relationship StudentID Name AssignmentID Age Student Obtain Assignment Mark PhoneNum Email Tittle Relational schema syntax: CREATE TABLE Student ( StudentID, Name, Age, Email, PhoneNum ); CREATE TABLE Assignment ( AssignmentID, Mark, Tittle ); CREATE TABLE Obtain ( StudentID, AssigmentID );