Assignment General Description
Task 1 not required, please quote for tasks 2,3,4
Qualification
Unit number and Title
Pearson BTEC Level 5HNC/HND Computing and Systems Development
HNCS 133 Data Analysis and Design
Student Name
Assessor
Internal Verifier
Date Issued
Submission Deadline for Task-1
Submission Deadline for Task-2
Submission Deadline for Task-3
Submission Deadline for
Task-4
Assignment title
Analysis, Design and Implementation of e-Business Database
Learning Outcome
Learning Outcome
Assessment Criteria
In this assignment you will have the opportunity to present evidence that shows you are able to:
Task no
Evidence
(Page no)
LO1
Understand data models and database technologies
1.1
critically compare different data models and schemas
1
1.2
critically discuss the benefits and limitations of different database technologies
1
1.3
analyse different approaches to database design
1
LO2
Be able to design and implement relational database systems
2.1
design a relational database system to meet a given requirement
2
2.2
build a relational database system based on a prepared design
2
2.3
apply a range of database tools and techniques to enhance the user interface
2
LO3
Be able to use manipulation and querying tools
3.1
explain the benefits of using manipulation and query tools in a relational database system
2
3.2
implement a query language into the relational database system
2
3.3
critically evaluate how meaningful data has been extracted through the use of query tools
2
LO4
Be able to test and document relational database systems
4.1
critically review and test a relational database system
4
4.2
create documentation to support the implementation and testing of a relational database system
4
4.3
create user documentation for a developed relational database system
4
4.4
explain how verification and validation has been addressed
4
4.5
explain how control mechanisms have been used.
4
Learner declaration
Guidelines for assignment submission
· The unit has 4 tasks. Please read ALL tasks carefully.
· Each Assignment has its own submission date. Please submit all Assignments ON TIME.
· Only ON TIME and EEC submission will be considered for a MERIT and DISTINCTION grade.
· All tasks MUST be submitted on time to be considered for a MERIT grade.
· The attendance percentage and participation in class lessonsand tutorials will contribute towards the achievement of Distinction grade.
· All assignments will be subject to PLAGIARISM checks. PLAGIARISM is a serious academic offence and will automatically lead to ACADEMIC MISCONDUCT PROCEDURES. In the event of COLLUSION (similar/identical submissions) please note that both students will automatically be subject to ACADEMIC MISCONDUCT PROCEDURES.
· When IPADS/TABLETS/MACS are used please make sure documents are saved as .doc. Otherwise the submission will not be valid and may not be marked.
e-Business Database Design
Database technologies are moving to the forefront of organisational marketing functions. Traditional approaches such as segmentation and positioning require information about customer characteristics and preferences. More recent approaches such as database marketing, relationship marketing, data mining and micro marketing depend on the collection of detailed information about customers, their preferences and purchase behaviour. Personalisations of content and product presentation are hallmarks of the most effective Ecommerce applications and are achieved through database system design.
To understand and exploit the potential of these approaches, the modern marketing manager must have a solid grasp of the basic functions of database applications. This assignment contains a series of exercises designed; 1) to develop students’ competence in basic and intermediate skills with Microsoft Access, and 2) to develop students’ competence in basic database design in support of e-Business activities. Specifically, you will design a system to record and analyse the operations of your e-Business team.
Task 1 NOT REQUIRED
Task-1
Preparation of Database Design (LO-1: 1.1, 1.2, 1.3 and M1, M3) (
You are required to produce a document for the following activities during analysis, design and implementation:
1. a. Critically discuss the benefits and limitations of different database technologies.[M1]
b. Discuss the advantages and limitations of the different notations and languages available to represent the analysis and design. [ P1.2 ]
2. Compare the effects and efficacy of the different approaches used to analyse and design databases. [P1.3]
3. Show the process of logical data modelling for your database. [ P1.1 ]
There are four key steps in data modeling [please refer to point 3 in reference list, chapter 16]:
a) Develop a logical data model for each known user view (form and report) for the application using normalisation principles.
b) Combine all normalised user views into one consolidated logical data model; this step is called view integration.
c) Translate the conceptual E-R data model for the application, developed without explicit consideration of specific user views, into normalised relations.
d) Compare the consolidated logical data model with the translated E-R model and produce, through view integration, one final logical data model for the application.
4. Model the database schema you need for this application [please refer to points 4 & 6 in reference list]. [ M1 ]
Answer for task 1 not required inserted for your information and reference
Database designing, implementation and analysing it from the complete picture before thesystems comes into real existence is very important. These steps should be done with proper care and looked after with good knowledge, as the initial steps if get failed will prove to be harmful in the long run. There we need to take several steps from the beginning such as:
1. What database models we should use
2. What kind of data structure would be worked upon
3. How we would be describing the structure
4. How much it would be helpful for us in long run
Apart from the above questions still there are many questions left around and need to answer while designing and developing the data, the resources who are going to work on the data should be equipped with proper knowledge and should have good implementation knowledge of the system.
There are different data models which we can use to work with the database as follows:
1. Hierarchical Model
2. Network Model
3. Relational Model
4. Associative model
5. Semi Structured Model
All the above models are very useful while doing the database development and we need to take proper care while selecting the data model.
1. Hierarchical model: In this type of models, data is organized in tree structure, and it is a hierarchy of parents and then its child relationship. It basically specifies that there are repeating information can be present under the child nodes, and data in a series of records, which have a set of field values linked to it. The record types which exist are equal of tables in the relational model; also it will have the individual records being the equal of rows. Link between different record is created based on parent child relationship, it has 1:N relationship structure.
2. Network Model: In this case any child can have more than one parent and this is why it is so called a parent. In this case it helps to model the data with more than one parent per child. So, the network model is permitting us to have the modelling of many to many relations on the data.
3. Relational Model: Relational model actually allows the use of data structures, saving the data and its retrieval operations and integrity constraints. In this case the relationship between the data is organized in a table and it helps to maintain the records without duplication and reduces the redundancy. A table is actually collection of records and every record in a table contains the same fields.
4. Associative Model: This model divide data into two sorts which will help to maintain the data in separate segments: Entities or tables become things in it which have separate presence. Entitys presence doesnt depend on any sort of other thing. Associations are things which existence depends on more than other things, such that if any of these things exist, then the thing itself ceases to exist or becomes meaningless.
5. Semi-Structured Model: Data in this kind of model actually become self describing data as the information which is normally associated with a schema is contained within the data. Data separation is not present in such a case and it is clear that data and the schemas are different sort of things. In some of the semi structured the schema is itself the same and there is no separate such a thing and its degree to which it is structured depends on the application type. In some forms there is no separate schema. Semi structured data is actually modelled in terms of graphs which eventually contains the labels which give semantics and then its underlying structure. (Database Answers)
From all of the above data models, the most common and popular data model are Relational data model is widely used worldwide and developed by E. F. Codd.
There are many different database technologies available which we can use to develop the database as follows:
1. MS SQL Server
2. My SQL Server
3. Oracle Server
4. Ms Access
All of the above database technologies are helpful for developing the database though they differ in some or the other features.
MySQL strength is that it actually supports most of the operating systems and is most commonly used for web applications. It is basically an open source database, which helps developers in view and improves the source code. MySQL is free from most of the users and even Oracle provides Enterprise Edition for this which has many enhanced features.
(Database Comparisons)
SQL Server was developed and designed by Microsoft and it helps many organizations ranging from small to medium sized in managing their daily operations. But it is not free and we need to buy license to use, and which is very costly. It would be including several security features on the database, which will not be available on the MySQL and it has several Job Scheduling features, SSIS, SSRS and many such advanced features.
(Database Comparisons)
Oracle is not common though many large scale organizations are using it for managing their large data, and importantly for UNIX servers which help them maintain their robust systems. Oracle is expensive and this is the reason small organizations are avoiding it, although it is reliable and has very promising results when managing data.
(Database Comparisons)
MS Access was designed by Microsoft under the Microsoft Office package, this is also a database management tool and it helps many individuals who dont have large business to support. Very small organizations or individuals usually work using MS Access database, and it has most of the relational database model features though it lacks under security features.
(Database Comparisons)
Database design is a technique which involved your complete knowledge of understanding the business requirement and also creating a design considering future requirement and future perspective if it needs to be modified. Database modifications are very costly if required in the future.
Following are techniques or softwares we can use to develop database design:
1. Microsoft Visio We can use it to draw ER Diagrams
2. IBM Rational Rose We can use it to draw ER Diagrams
3. We can also develop Enhanced Entity Relationship Diagrams which will be helpful to display the database strength.
4. We can also develop Database Relationship diagram, which is very easy to understand and complete, it will help us in determining all the required entities and attributes in the database.
So, leveraging database schemas, their structure and overall designing and development needs lots of effort from the beginning till the end, it will definitely dependent on your learning how you leverage that and how you decide the model and structure of the complete database.
References:
Database Answers, Database Models Page,.databaseanswers.org/data_models/”>http://www.databaseanswers.org/data_models/
Comparisons Page, W3 Computing Database Comparisons Page,.w3computing.com/sqlserver/comparison-oracle-db2-mysql-sql-server/”>http://www.w3computing.com/sqlserver/comparison-oracle-db2-mysql-sql-server/
TASK 2
Task-2
Designing a Basic e-Business Database System (LO-2: 2.1, 2.2, 2.3 and LO-3: 3.1, 3.2, 3.3) (Submission Deadline: Week 12)
1. a. Using Chen’s notation draw an Entity Relationship Diagram (ERD) to describe your database system.
You must include this diagram as part of the assignment [see point 3 (chapter 10) and point 5 in reference list]
b. Clearly indicate how you used your ERD to plan the tables that you created.
[ P 2.1 ]
Part I: Creating Basic Database Objects
In the following two exercises, you will build a simplified order entry database for your e-Business with Microsoft Access. While the final product will not be a complete, comprehensive system, it will provide a good orientation to the design of such a system. In Part 1, you will build the basic database objects (tables, queries, forms, reports) for the system.
If necessary, you should complete the Introductory Access tutorial to help you with this exercise.
Using the appropriate object functions in Microsoft Access, build a database, 575FML.mdb (where FML are the initials of your first, middle and last names), with following objects. Populate each with the designated number of fictitious records. Primary key fields are designated with an asterisk and should be formatted as AutoNumber fields.
Tables
[ P 2.2 ]
tblCustomers with fields for dblCustID#*, txtFirstName, txtLastName, txtAddress, txtCity, txtState, txtZIP, txtPhone, txtEMail (15 records)
tblTeamMembers with fields for MemID#*, txtFirstName, txtLastName, txtEMail (1 record for each member of your team)
tblProductswith fields for dblProdID#*, txtProdName, txtProddblCost (8 records)
tblAuctions with fields for dbleBayAuc#*, datCloseDate, dblCustID#, dblMemID#, dblProdID#, cur Price, curListingFee, curShippingCost (10 records)
tblWebOrders with fields for dbl Order#*, datOrderDate, dblCustID#,dblMemID#, dblProdID#, curPrice, dblQuantity, curShippingCost (10 records)
tblSubscribers(imported from your FrontPage form database) with fields for dblSubID#, txtFirstName, txtLastName, txtEMail and other fields collected by your form
You must; 1) define data types, enter captions and provide descriptions for each field, 2) define input masks for all date, phone and ZIP fields, 3) limit the txtState field to two character abbreviations, and 4) define relationships between tables with the Relationship Tool.
Queries
1. Explain the benefits of using manipulation and query tools in a relational database system. [ P 3.1 ]
2. Create a query qryAuctionOrderInfo based on the tblAuctionOrders, tblCustomers and tblProducts tables which lists orders information (datCloseDate, dblCustID#, txtCustomerName [an expression txtCustomerName: [txtFirstName] & ‘ ‘ [txtLastName]], dblProdID#, txtProdName, curPrice, txtProdCost, curListingFee, curShippingCost and curOrderMargin [a calculated field, (Price-Product Cost-ListingFee-ShippingCost)] sorted by date. [ P 3.2 ]
3. Critically evaluate how meaningful data has been extracted through the use of query tools. [ P 3.3 ]
References:
1. Consult your class materials including eBooks provided during the class and tutorial sessions.
2. My Lecture Notes on UML
3. Joeffrey A. Hoffer et al., Modern Systems Analysis and Design, 2nd Edition, 1999 by Addison and Wesley
4. .databaseanswers.org/data_models/”>http://www.databaseanswers.org/data_models/ß Library of data models and schemas
5. .wikipedia.org/wiki/Entity-relationship_model”>http://en.wikipedia.org/wiki/Entity-relationship_modelß Entity-relationship diagram using Chen’s notation
6. .asp.net/scottgu/archive/2006/07/12/Tip_2F00_Trick_3A00_-Online-Database-Schema-Samples-Library.aspx”>http://weblogs.asp.net/scottgu/archive/2006/07/12/Tip_2F00_Trick_3A00_-Online-Database-Schema-Samples-Library.aspx ß Online Database Schema Samples Library
7. .tomjewett.com/dbdesign/dbdesign.php”>http://www.tomjewett.com/dbdesign/dbdesign.php ß Database design with UML and SQL, 3rd edition
8. .microsoft.com/en-gb/access-help/create-and-use-a-switchboard-HA001213876.aspx”>http://office.microsoft.com/en-gb/access-help/create-and-use-a-switchboard-HA001213876.aspx ß Create and use a switchboard
9. .brighthub.com/computing/windows-platform/articles/33793.aspx”>http://www.brighthub.com/computing/windows-platform/articles/33793.aspx ß Using the Switchboard to Navigate Your Database
10. .techonthenet.com/access/switchboard/create2007.php”>http://www.techonthenet.com/access/switchboard/create2007.php ß Create a switchboard in Access 2007
11. .youtube.com/watch?v=rb1k_6ykR9k”>http://www.youtube.com/watch?v=rb1k_6ykR9kß Video tutorial on creating a Custom Switchboard in Access 2007
.grussell.org/index.html”>http://db.grussell.org/index.html ß Database eLearning
