Flashy DB Docs

z
COLLEGE OF INFORMATION TECHNOLOGY
Entity Relationship Diagram, Database Normalization and Data
Dictionary of Flashy flashcard learning android mobile
application Presented to the Faculty of College of
Information Computer Technology
Nueva Ecija University of Science and Technology
General Tinio (Papaya) Off Campus Program
MEMBERS
Rosete, Dan Ivan Patricio
Sanao, Raven Tavu
De Leon, Jennalyn
z
Entity Relationship Diagram

z
Database Name: flashy_db

Tables: users, friends, sets, cards, share_sets
users table:
Field Type Null Key Default Extra

id int(11) No PRI NULL auto_incerement
username varchar(50) No UNI NULL
password varchar(32) No NULL
alias varchar(10) No UNI NULL
first_name varchar(50) No NULL
last_name varchar(50) No NULL
sets table:

set_title varchar(50) No NULL
set_description varchar(255 No NULL
)
touched varchar(50) No NULL
status varchar(10) No NULL
shared tinyint(1) No NULL
owner_id Int(11) No MUL NULL
cards table:

card_title varchar(50) No NULL
card_description text No NULL
image longtext No NULL
set_id int(11) No MUIL NULL
friends table:

user_id int(11) No NULL
friend_id int(11) No NULL
z
share_sets table:

permission varchar(10) No NULL
share_to_id int(11) No MUL NULL
owner_id int(11) No MUL NULL
set_id int(11) (50) No MUL NULL
NORMALIZING USERS TABLE
users table:
username password alias first_name last_name friends_first_name friends_last_name

user1 pass1234 user1 user1 first user1 last user2 first name, user2 last name,
name name user3 first name user3 lastname
user2 password user2 user2 first user2 last user1 first name user1 last name
name name
user3 strongpasswor user3 user3 first user3 last user1 first name, user1 last name,
d name name user2 first name user2 last name
Current user table doesn’t have a primary key and poses

multiple values in one column.
First normal form
users table:
username password alias first_name last_name friend_first_name friend_last_name

user1 pass1234 user1 user1 first user1 last user2 first name user2 last name
name name
user1 pass1234 user1 user1 first user1 last user3 first name user3 lastname
name name
user2 password user2 user2 first user2 last user1 first name user1 last name
name name
user3 strongpassword user3 user3 first user3 last user1 first name, user1 last name
name name
user3 strongpassword user3 user3 first user3 last user2 first name user2 last name
name name
z
Possible partial dependency:
username -> password, alias, first_name, last_name
Possible transitive dependency:
{first_name, last_name} -> friends_first_name,

friends_last_name
Second normal form
Column friend_first_name and friend_last_name is dependent

on the value of column first_name and last_name. The data is
prone to inconsistency. The next form proposes new approach.
users table:
Id(PK) username password alias first_name last_name

1 user1 pass1234 user1 user1 first user1 last name
name
2 user2 password user2 user2 first user2 last name
name
3 user3 strongpassword user3 user3 first user3 last name
name
friends table:
Id(PK) user_id friend_id

1 1 2
2 1 3
3 2 1
4 3 1
5 3 2
z
By using this approach, we can eliminate the inconsistency

as well as the possible transitive dependency. Therefor
third normal form is not needed.
NORMALIZING SETS TABLE
sets table:
current sets table doesn’t have a primary key, and prone to

redundancy as we add more cards to the same set of
flashcards.
First normal form

Applied a column id as primary key which uniquely identify
each column.
Second normal form

Partial dependency:
username, password, alias, first_name, last_name
Id(P set_titl set_descrip touched stat shar owner card_ti card_descrip image
K) e tion us ed _id tle tion
1 flashcar flashcard1 1702823 acti 0 1 card1 card1 sample.
d1 description 923 ve description png
We already have a separate table for users, we can then use
the id from users table and apply the foreign key
Third normal form

z
We need to eliminate transitive dependencies by decomposing
the table into two parts, the sets and cards.
Id(PK) set_title set_description touched status shared owner_id

1 flashcard1 flashcard1 1702823923 active 0 1
description
description
description
cards table:
Id(PK) card_title card_description image set_id

1 card1 card1 description sample.png 1
By performing these normalization steps, we achieve at least
the Second Normal Form (2NF) and Third Normal Form (3NF).
Each table has a clear primary key, and non-prime attributes
are fully functionally dependent on the primary key.
NORMALIZING SHARE SETS TABLE
share_sets table:
share_to_user_id user_id set_id permission
1 3 3 view
2 2 2 edit
3 1 1 edit
Current share sets table doesn't have a primary attribute to
uniquely identify the rows.

z
First normal form
Id(PK) share_to_user_id user_id set_id permission

1 1 3 3 view
2 2 2 2 edit
3 3 1 1 edit
Primary key column id is applied to uniquely identify each
of the rows. 2nd and 3nd normal form are skipped since the
current table doesn't have a partial and transitive
dependency.

Flashy DB Docs

Uploaded by

Copyright:

Available Formats

Flashy DB Docs

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Flashy DB Docs

Uploaded by

Copyright:

Available Formats

z

COLLEGE OF INFORMATION TECHNOLOGY

Entity Relationship Diagram, Database Normalization and Data

Dictionary of Flashy flashcard learning android mobile

application Presented to the Faculty of College of

Information Computer Technology

Nueva Ecija University of Science and Technology

General Tinio (Papaya) Off Campus Program

Rosete, Dan Ivan Patricio

Sanao, Raven Tavu

COLLEGE OF INFORMATION TECHNOLOGY

Entity Relationship Diagram

COLLEGE OF INFORMATION TECHNOLOGY

Database Name: flashy_db

Field Type Null Key Default Extra

Field Type Null Key Default Extra

Field Type Null Key Default Extra

Field Type Null Key Default Extra

COLLEGE OF INFORMATION TECHNOLOGY

Field Type Null Key Default Extra

NORMALIZING USERS TABLE

username password alias first_name last_name friends_first_name friends_last_name

Current user table doesn’t have a primary key and poses

First normal form

username password alias first_name last_name friend_first_name friend_last_name

COLLEGE OF INFORMATION TECHNOLOGY

Possible partial dependency:

username -> password, alias, first_name, last_name

Possible transitive dependency:

{first_name, last_name} -> friends_first_name,

Second normal form

Column friend_first_name and friend_last_name is dependent

Id(PK) username password alias first_name last_name

Id(PK) user_id friend_id

COLLEGE OF INFORMATION TECHNOLOGY

By using this approach, we can eliminate the inconsistency

NORMALIZING SETS TABLE

current sets table doesn’t have a primary key, and prone to

First normal form

Second normal form

We already have a separate table for users, we can then use

the id from users table and apply the foreign key

Third normal form

COLLEGE OF INFORMATION TECHNOLOGY

We need to eliminate transitive dependencies by decomposing

the table into two parts, the sets and cards.

Id(PK) set_title set_description touched status shared owner_id

Id(PK) card_title card_description image set_id

By performing these normalization steps, we achieve at least

Each table has a clear primary key, and non-prime attributes

are fully functionally dependent on the primary key.

NORMALIZING SHARE SETS TABLE

share_to_user_id user_id set_id permission

Current share sets table doesn't have a primary attribute to

uniquely identify the rows.

COLLEGE OF INFORMATION TECHNOLOGY

First normal form

Id(PK) share_to_user_id user_id set_id permission

Primary key column id is applied to uniquely identify each