Expert answer:Data Storage Organization Physical Database Design
Answer & Explanation:You’ve been hired as a database consultant for a
start-up company that will stream movies over the Internet (similar to
Hulu, Netflix, and others).Use the following document Database Design ProjectDefine data storage organization for the physical design of the database.Make sure any referenced sources are properly cited.
learning_team_c___week_5.docx
Unformatted Attachment Preview
Running head: LEARNING TEAM C
1
Learning Team C
Angela Moore, Brandon Lester, Carl Dodge, James Bowlin, Lori Filz
DBM/502
September 14, 2015
Reggie Haseltine
LEARNING TEAM C
2
Learning Team C
FlixMyWay is a startup company offering movie streaming over the Internet to its
customers. As a consulting company, FlixMyWay has hired us to determine what database
requirements will be required for the successful operation of FlixMyWay.
We recommend one database be created with the following tables to store the necessary
information to support the FlixMyWay operations.
CUSTOMERINFO TABLE
Column Name
Key
Type
Size
Required
Value
Range
Default
Value
Description
Number
10
Yes
NULL
Unique Customer ID (Increment by 1)
CustFirstName
Character
30
Yes
NULL
Customer’s First Name
CustLastName
Character
40
Yes
NULL
Address1
Character
40
Yes
NULL
Customer’s Last Name
Customer Address Line 1
Address2
Character
40
No
NULL
Customer Address Line 2
City
Character
40
Yes
NULL
Customer City
State
CustomerID
Primary
Data
Type
Character
40
Yes
NULL
Customer State
Zip
Number
5
Yes
NULL
Customer Zip
CreditCardNo
Number
16
No
NULL
Customer Credit Card Number
CreditCardExp
Date
10
No
NULL
Customer Credit Card Expiration
GenreLike1
Character
10
No
Selection
NULL
Genre Customer Likes #1
GenreLike2
Character
10
No
Selection
NULL
Genre Customer Likes #2
GenreLike3
Character
10
No
Selection
NULL
Genre Customer Likes #3
MOVIEINFO TABLE
Column
Name
MovieID
SupplierID
MovieTitle
MovieYear
MovieDesc
Genre
RentalCost
DateAdded
RentalCount
Key
Type
Data
Type
Size
Required
Primary
Foreign
Number
Number
Character
Number
Character
Character
15
15
40
4
250
10
Yes
Yes
Yes
Yes
Yes
Yes
Currency
4
Yes
Date
Number
10
6
Yes
Yes
Value
Range
Selection
00.01 20.00
Default
Value
Description
NULL
NULL
NULL
NULL
NULL
NULL
Movie ID (Unique)
Supplier ID of Movie Owner
Title of Movie
Year the Movie was released
Movie Description
Movie Genre
NULL
Movie Rental Cost
NULL
NULL
Date Movie Was Added for Rent
Count of Movie Rentals
LEARNING TEAM C
3
SUPPLIERINFO TABLE
Key
Type
Primar
y
Column Name
SupplierID
Data
Type
Siz
e
Require
d
Number
15
Yes
NULL
40
Yes
NULL
40
Yes
NULL
Customer Address Line 1
40
No
NULL
Customer Address Line 2
40
Yes
NULL
40
Yes
NULL
5
9
Yes
Yes
NULL
NULL
17
Yes
NULL
Characte
r
Characte
r
Characte
r
Characte
r
Characte
r
Number
Number
SupplierName
SupAddress1
SupAddress2
SupCity
SupState
SupZip
SupBankRouting
SupBankAccoun
t
Number
Value
Range
Default
Value
Description
Supplier ID
Movie Supplier Name
Customer City
Customer State
Customer Zip
Supplier Bank Routing Number
Supplier Bank Account Number
RENTALHISTORY TABLE
Column
Name
UniqueKey
CustomerID
MovieID
DateRented
Key
Type
Data
Type
Size
Required
Primary
Foreign
Foreign
Number
Number
Number
Character
20
15
15
40
Yes
Yes
Yes
Yes
Value
Range
Default
Value
NULL
NULL
NULL
NULL
Description
Unique Key incremented by 1
Unique Customer ID (Increment by 1)
Movie ID (Unique)
Date rented by customer
LEARNING TEAM C
4
Entity and Relationship Diagram
Based on the requirements outline above, we were able to illustrate FlixMyWay entity
and relationship diagram. Figure 1.1 below is the entity and relationship diagram.
CustomerInfo
RentalHistoryInfo
PK
CustomerID
CustFirstName
CustLastName
UniqueKey
PK
Made
By
Address1
MovieInfo
Stored
In
Makes
MovieID
PK
FK
CustomerID
MovieTitle
FK
MovieID
MovieYear
DateRented
Stores
MovieDesc
Address2
Genre
City
RentalCost
State
DateAdded
SupplierInfo
Supplies
Zip
PK
RentalCount
SupplierID
CreditCardNo
FK
SupplierName
CreditCardExp
SupAddress1
GenreLike1
SupAddress2
GenreLike2
SupCity
GenreLike3
SupState
SupZip
SupBankRouting
SupBankAccount
Supplied
By
SupplierID
LEARNING TEAM C
5
Data Model Verification
In order to make sure that our ERD is both logical and true against the requirements of
the business we must validate it. As a part of the verification process we will create a sample
customer history report in the table below and validate the report against our ERD model.
CUSTOMER NO.: 24573
NAME: John Smith
ADDRESS: 1564 10th ST.
Detroit, MI 45635
RENTAL
MOVIE
HISTORY #
TITLE
1524
Spaceballs
24365
Ghostbusters
MOVIE
DESCRIPTION
Spoof of Star Wars
Group of guys
battle ghosts
SUPPLIER
MGM
Columbia
Pictures
RENTAL
DATE
07/24/2010
10/31/2013
RENTAL
COST
$4.99
$2.99
Report: Customer Rental History
The Customer Rental History involves four entities:
CUSTOMERINFO
RENTALHISTORYINFO
MOVIEINFO
SUPPLIERINFO
The primary entity is RENTALHISTORYINFO.
The data items are accounted for in the E-R Diagram as follows:
•
CUSTOMER NO: attribute CustomerID of entity CUSTOMERINFO.
o CUSTOMERINFO is linked to RENTALHISTORYINFO via the CustomerID foreign
key in RENTALHISTORYINFO.
•
NAME: attributes CustFirstName and CustLastName of entity CUSTOMERINFO.
o CUSTOMERINFO is linked to RENTALHISTORYINFO via the CustomerID foreign
key in RENTALHISTORYINFO.
•
ADDRESS: attributes Address1, Address2, City, State, and Zip of entity CUSTOMERINFO.
LEARNING TEAM C
6
o CUSTOMERINFO is linked to RENTALHISTORYINFO via the CustomerID foreign
key in RENTALHISTORYINFO.
•
RENTALHISTORY #: attribute UniqueKey of entity RENTALHISTORYINFO.
•
MOVIETITLE: attribute MovieTitle of entity MOVIEINFO.
o MOVIEINFO is linked to RENTALHISTORYINFO via the MovieID foreign key in
RENTALHISTORYINFO.
•
MOVIEDESCRIPTION: attribute MovieDesc of entity MOVIEINFO.
o MOVIEINFO is linked to RENTALHISTORYINFO via the MovieID foreign key in
RENTALHISTORYINFO.
•
SUPPLIER: attribute SupplierName of entity SUPPLIERINFO.
o SUPPLIERINFO is linked to MOVIEINFO via the SupplierID foreign key in
MOVIEINFO.
o MOVIEINFO is linked to RENTALHISTORYINFO via the MovieID foreign key in
RENTALHISTORYINFO.
•
RENTALDATE: attribute DateRented of entity RENTALHISTORYINFO.
•
RENTALCOST: attribute RentalCost of entity MOVIEINFO.
o MOVIEINFO is linked to RENTALHISTORYINFO via the MovieID foreign key in
RENTALHISTORYINFO.
Based upon our analysis, it is possible to create the customer rental history report without
errors. Therefore, our ERD model has been verified to be an accurate representation of the entity
relationships for FlixMyWay’s database systems.
LEARNING TEAM C
7
Database Design with Distributed or non-distributed
A distributed database is recommended for web applications services over the Internet
such as FlixMyWay and their movie streaming. Replication will occur to keep the information
updated centrally and across all instances of the database. The replication will allow the
customer to access their FlixMyWay information from anywhere they may travel. Multiple
copies will also allow for accessibility at all times should a copy become unavailable at a certain
location.
Logical model using normalization
1. CustomerInfo Table
1NF- CustomerInfo Table is already in first normal form
2NF- CustomerInfo Table is not in 2NF form because:
Credit card number depends on customer name, not address
CustomerID
CustFirstName
CustLastName
Address1
Address2
City
State
Zipcode
CustomerID
CreditCardNo
LEARNING TEAM C
8
CreditCardExp
MembershipStartDate
MembershipExpDate
3NF- CustomerInfo Table is not in third form because:
Membership depends on credit card info, not customer name
MembershipStartDate
MembershipExpDate
CreditCardNo
CreditCardExp
2. RentalHistory Table
1NF- RentalHistory table is in first normal form because:
It does not have duplicate records, each cell has single value, and each record is unique
2NF- RentalHistory table is in second normal form because:
All non- key fields depend on all the components of the primary key
3NF- RentalHistory table is in third from because:
All the non-key fields depends on the primary key
3. MovieInfo Table
1NF- MovieInfo table is in first normal form because:
It does not have duplicate records, each cell has single value, and each record is unique
2NF- MovieInfo table is in second normal form because:
All non- key fields depend on all the components of the primary key
LEARNING TEAM C
9
3NF- MovieInfo table is in third from because:
All the non-key fields depends on the primary key
4. SupplierInfo Table
1NF- SupplierInfo table is in first normal form because:
It does not have duplicate records, each cell has single value, and each record is unique
2NF- SupplierInfo table is in second normal form because:
All non- key fields depend on all the components of the primary key
3NF- SupplierInfo table is not in third form because:
City and state depends on the Zipcode, not primary key
SupplierID
Zipcode
City
State
Logical Model Integrity Constraints
CustomerInfo Table must have all fields completed. No Null Values are allowed.
Duplicate customer names are allowed but CustomerID must be unique.
RentalHistory Table must have all fields completed. No Null Values are allowed. Duplicate
MovieIDs are allowed as there will be more than one copy of each movie available. Foreign key
CustomerID and MovieID match primary keys in CustomerInfo Table and MovieInfo Table.
MovieInfo Table must have all fields completed. No Null Values are allowed. Foreign key
SupplierID is linked to the primary key in SupplierInfo Table.
LEARNING TEAM C
10
SupplierInfo Table must have data in all fields except SupAddress2 field. SupAddress2 is
allowed null value.
Validation of Logical Model against the Customer Requirements
The customer requirements are listed in the table below. The required data highlighted is
not currently part of the logical design. We will need to add five fields to the CustomerInfo table
to capture up to five movie titles that customer is interested. The five MovieTitle fields can be
null. In addition a MembershipStartDate and MembershipExpDate has been included as a result
of the logical design.
Data Category
Customer Information
Movie Information
Supplier Information
Data Required
Customer name
Customer address
Customer ID
Credit card number
Credit card expiration
history of movies rented
“likes” (such as, genres, and movies the customer is interested
in)
Movie title
Year Released
Supplier ID (owner of movie)
Description of movie
Statistics on movie rental frequency
Rental cost
Supplier ID
Supplier name
Supplier address
Supplier bank routing payment information
LEARNING TEAM C
11
Defining integrity and security measures
To ensure physical and logical integrity of the database for FlixMyWay’s is to ensure
data entries from destruction by implementing a backup and data recovery process and plan.
Ensuring integrity of each data element inside the FlixMyWay’s database is critical. To achieve
this goal is to make sure each element can only be changed or modified by authorized users to
enter correct and accurate values for each data element. To ensure security measures for
FlixMyWay’s database is to ensure there is proper documentation and access control written in
the security policy by the database administrator. The security policy should state who has
access to certain tables, table restrictions, server security and connections to the database.
Ensuring valid authorization and authentication for authorized users is paramount to the
security of FlixMyWay’s database. There should be authentication methods and user
identification administered by the database administrator of FlixMyWay’s company. The process
should be outlined and detailed enough to ensure users know and understand how they will be
authenticated in the database. Encryption and database password protection should be described
if implemented to protect the database and the data within the database. Lastly, availability needs
to be paramount for users to be able to be authenticated and authorized to access required data
(Burtescu, 2009).
Determine Performance Measures
The DBA is responsible for monitoring the performance of the databases and servers
running the databases. To do monitoring, information must be collected and analyzed regularly.
The four main performance areas to monitor include:
1. Response times for frequently used queries
2. User connections to a SQL Server instance
LEARNING TEAM C
12
3. Availability and use of system resources such as CPU and Memory
4. Performance of application components
The tools used to collect the information for performance monitoring can be found by using
SQL utilities and Windows server performance tools. The SQL utilities frequently used are the
SQL Server Profiler and SQL Trace and Activity Monitor. The Windows server tools are the
performance monitor, task manager, performance logs, and performance alerts.
Listed below are the common causes of performance that should be included in any
monitoring and analysis performed by the DBA:
•
Blocking locks – If transactions hold locks on rows too long, they block other
transactions from processing against those rows.
•
Disk Input/Output (I/O) – Excessive disk I/O slows down transactions. Excessive disk
I/O can be reduced if sufficient table indexes are created.
•
Server capacity – If the server has reached capacity, transactions will slow.
•
Memory usage – Memory is used to cache data so it can be accessed quickly. Having
sufficient memory allows data to be accessed quickly.
•
CPU utilization – High CPU usage indicates queries are not properly tuned or an increase
in processor power is needed.
Database Backup and Recovery
No matter how well your database is designed, a database failure is almost inevitable.
Human error, hardware failure, and network failures are just a few ways a database could fail.
Since the failure cannot be avoided, the backup and recovery process must be strong to get the
database back up and running as soon as possible. Downtime must be minimized to ensure key
business processes can continue to take place.
LEARNING TEAM C
13
Backup facility provides periodic backup copies of portions of the database or the entire
database. If there is a failure at noon, the database can be recovered from 11am. Losing one hour
of work opposed to months is always a favorable choice. Database objects as well as the
repository, source libraries and indexes are copies. Incremental backups will record changes
since the last full backup. This saves a lot more time than doing a full system backup.
Journalizing facilities provide the DBMS with an audit trail of transactions and database
changes. If the database does fail, you can go to the last complete database backup as a starting
point. With the journal entries all of the missing transactions and changes can be replaced.
In order to recover the database in the event of a failure, procedures must be in place to minimize
the downtime. Disk mirroring is one of these procedures. During disk mirroring, two copies of
the databases are kept and updated at the same time. RAID technology is used to hot swap in the
case of a failure. There is no interruption to the user and the processing of the database is
automatically switched over to one of the mirrored images.
LEARNING TEAM C
14
References
Burtescu, E. (2009). Database Security- Attacks and control methods. Journal of Applied
Quantitative Methods. Retrieved from http://www.jaqm.ro/issues/volume-4,issue4/pdfs/burtescu.pdf
Skillsoft (n.d.). Microsoft SQL Server 2012: Database Monitoring and Performance Tuning
[Video podcast]. Retrieved from
https://uopx.skillport.com/skillportfe/assetSummaryPage.action?assetid=md_msdb_a08_i
t_enus&fromShare=yes
…
Purchase answer to see full
attachment
How it works
- Paste your instructions in the instructions box. You can also attach an instructions file
- Select the writer category, deadline, education level and review the instructions
- Make a payment for the order to be assignment to a writer
- Download the paper after the writer uploads it
Will the writer plagiarize my essay?
You will get a plagiarism-free paper and you can get an originality report upon request.
Is this service safe?
All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades