Database Design
Module Booklet
Unit | Unit17: Database Design Concepts |
Unit level | |
Unit code | R/601/0447 |
Credit Value | 15 |
Qualification | BTEC HND in Computing and Systems Development |
Term Start Date | 05 June 2017 |
Hand out date | |
Submission Deadline | 13 August 2017 |
Lecture | 7 weeks |
Revision & Formatives | 3 Weeks |
Guided Learning hours | 60 |
Module Leader | Dr. Evan Tzanis |
Lecturer |
|
CONTENTS
1.0 INTRODUCTION 3
2.0 Unit / Module Content 4
3.0 Learning Outcomes and assessment criteria: 5
4.0 Programme Specification/ Schemes of work 6
5 Recommended text and links 11
6.0 Assignment Brief Section 15
7 Evidence Checklist 19
8 Contextualisation grid to achieve higher grade 21
9 Assignment Guidelines: 23
10 Glossary of academic words used in this and other assignments 24
Databases play an integral part in commercial domains, they provide users with a tool in which to store, model and retrieve data. Database development is fundamental in the area of computing and ICT within organisational contexts. Database Management Systems (DBMS) provide the systems, tools and interfaces by which the organization can manage their information and use it to assist in the effective running of the organization. Databases offer many links to others such as programming, systems analysis, HCI, as well as embracing issues of compatibility and end-user interfacing.
This unit explores database architecture, DBMS and the use of databases in an organizational context. Database design techniques are investigated and successful learners will be able to apply theoretical understanding to design, create and document a database system.
1.1 Aim:
To give learners opportunities to develop an understanding of the concepts and issues relating to databases and database design as well as the practical skills to translate that understanding in to the design and creation of complex databases.
1.2 Objectives:
On successful completion of this unit a learner will :
1. Understand databases and data management systems
2. Understand database design techniques
3. Be able to design, create and document databases.
2.0 Unit / Module Content
1 Understand databases and data management systems
Databases: database architectures; files and record structures; physical and logical views of data; advantages of using databases; reduction of data redundancy; data consistency (validity, accuracy, usability and integrity); independence of data; data sharing possibilities; security; enforcement of standards; database utilities; data dictionaries; query languages; report generators.
Databases in an organizational context: database applications; role of the database
Administrator; key organizational issues eg integrity, security, recovery, concurrency; industry standards e.g. Microsoft SQL, Oracle, Sybase, dBase
Database Management Systems (DBMS): structures; purposes; features and advantages; Applications; methods of data organization and access
2 Understand database design techniques
Database design methods and methodology: requirements analysis; database designer working with expert in domain development area; requirement specification; logical design e.g. relational databases, tables; physical design e.g. data elements, data types, indexes; data analysis and design within systems analysis; database design within a system development methodology
Relational database design: tables, relations, primary/foreign/compound keys; entity relationship modeling; normalization theory to third normal form
3 Be able to design, create and document databases
Database development cycle: developing logical data model; implementing a physical data model based on the logical data model; testing the physical data model; comparing model with requirements analysis; user interface e.g. input masks, drop-down lists, option buttons, command buttons
Database software: using appropriate applications software, e.g. Microsoft Access, SQL; database tools eg create tables, add new rows, and alter data, functions, and relational database languages
Tools and techniques: field and table design; validation and verification techniques; forms including such features as dropdown lists or check boxes; reports; queries; macros
Documentation: technical documentation; user documentation.
On successful completion of this unit a learner will:
LO1 Understand databases and data management systems
1.1 analyse the key issues and application of databases within organizational environments.
1.2 critically evaluate the features and advantages of database management systems.
LO2 Understand database design techniques
2.1 analyse a database developmental methodology
2.2 discuss entity-relationship modeling and normalization
LO3 Be able to design, create and document databases
3.1 apply the database developmental cycle to a given data set
3.2 design a fully functional database (containing at least four inter-relational tables) including user interface
3.3 evaluate the effectiveness of the database solution and suggest methods of improvement
3.4 provide supporting user and technical documentation
Accrediting Body: EDEXCEL
Course: BTEC HND IN Computing and Systems Development
Unit: 17Database Design Concepts
Week | Session | Lecture Schedule | Outcome of session | Activity/seminar and formative assessment | Resources |
AM | Introduction to Database Design Concepts. | Introduction to the module, mode of assessment, awareness of the awarding body, assessment criteria and the mode of assessment. | Role play and individual induction, helping to know each other. 1 hour workshop & Activity session | Module handbook. | |
PM | Databases: database architectures; files and record structures; physical and logical views of data; advantages of using databases; reduction of data redundancy; data consistency (validity, accuracy, usability and integrity); | By the end of the session you will be able to Understand:
| Case study, tutorial and addressing the partly learning outcome 1.1 1 hour workshop & Activity session | Power point slides from: Connolly T and Begg C, and C J Date, Research | |
AM | Databases: independence of data; data sharing possibilities; security; enforcement of standards; database utilities; data dictionaries; query languages; report generators | By the end of the session you will be able to Understand:
| Case study, tutorial and addressing the partly learning outcome 1.1 1 hour workshop & Activity session | Power point slides from: Connolly T and Begg C, and C J Date, Research | |
PM | Databases in an organisational context: database applications; role of the database administrator; key organisational issues e.g. integrity, security, recovery, concurrency; industry standards e.g. Microsoft SQL, Oracle, Sybase, dBase | Learners will be able to know the DBMS solution for Enterprise application and Industry standards DBMS software. | Case study, tutorial and addressing the partly learning outcome 1.1 and 1.2 1 hour workshop & Activity session | Power point slides from: Connolly T and Begg C, and C J Date, Research | |
AM | Database design methods and methodology: physical design eg data elements, data types, indexes; data analysis and design within systems analysis; database design within a system development methodology Tools and techniques: field and table design; validation and verification techniques; | Learners will be able to know and apply methodology, different data types to design database. | Tutorial and address the LO 2.1. & Partly 3.2 1 hour workshop & Activity session | Course Notes, Research | |
PM | Database design methods and methodology: requirements analysis; database designer working with expert in domain development area; requirement specification; logical design eg relational databases, tables; | Learners will be able to apply constraints in Logical and design and develop relational database solution. | Tutorial using MS Excel and Access addressing the learning outcome 2.1 1 hour workshop & Activity session | Course Notes, Practical | |
AM | Database Management Systems (DBMS): structures; purposes; features and advantages; applications; methods of data organization and access | Learners will be able to understand the following:
Access of Data. | Case study, tutorial and addressing the partly learning outcome 1.2 1 hour workshop & Activity session | Course Notes, Practical | |
PM | Relational database design: tables, relations, primary/foreign/compound keys; entity relationship modeling; normalization theory to third normal form | Learners will be able to design database by applying normalization, create logical tables with primary, foreign and compound keys and ER modeling. | Tutorial and address partly the LO 2.2 1 hour workshop & Activity session | Course Notes, and Lab Practical, online video tutorial. | |
AM | Relational database design: tables, relations, primary/foreign/compound keys; entity relationship modeling; normalization theory to third normal form | Learners will be able to design database by applying normalization, create logical tables with primary, foreign and compound keys and ER modeling. | Tutorial and address partly the LO 2.2 Case Study to make a complete ERD of 1 hour workshop & Activity session | Course Notes, and Lab Practical, online video tutorial. | |
PM | Database development cycle: developing logical data model; implementing a physical data model based on the logical data model; testing the physical data model; comparing model with requirements analysis; user interface eg input masks, drop-down lists, option buttons, command buttons | Learners will be able to Develop physical database tables and forms using MS Access 2007. | Tutorial and address the LO 3.1 1 hour workshop & Activity session | Course Notes, and Lab Practical, online video tutorial. | |
AM | Database software: using appropriate applications software, e.g. Microsoft Access, SQL; database tools e.g. create tables, add new rows, alter data, functions, relational database languages | Learners will be able to Develop database queries and reports MS Access 2007. | Tutorial and address the LO 3.2 1 hour workshop & Activity session | Course Notes, and Lab Practical, online video tutorial. | |
PM | Tools and techniques: field and table design; validation and verification techniques; | Learners will be able to apply validation and verification techniques for the developed database solution. | Tutorial and address the LO 3.3 1 hour workshop & Activity session | Course Notes, and Lab Practical, online video tutorial. | |
AM | Tools and techniques: forms including such features as dropdown lists or check boxes; reports; queries; macros | Learners will be able to apply validation and verification techniques for the developed database solution. | Tutorial and address the LO 3.3 1 hour workshop & Activity session | Course Notes, and Lab Practical, online video tutorial. | |
PM | Documentation: technical documentation | Learners will be able to create documentation for developed database solution and user of the application. | Tutorial and address the LO 3.4 1 hour workshop & Activity session | Course Notes, and Lab Practical, online video tutorial. | |
AM | Revision of LO 1 & 2 | ||||
PM | Revision of LO 2 & 3 | ||||
AM | Assessment Support | ||||
PM | Assessment Support | ||||
10 | AM | Assessment Support & upload | |||
PM | Assessment Support & upload |
Books
Avison D and Fitzgerald G – Information Systems Development: Methodologies, Techniques and Tools (McGraw Hill Higher Publishing Company, 2006) ISBN 0077114175
Chao L – Database Development and Management (CRC Press, 2006) ISBN 0849392381
Connolly T and Begg C – Database Systems: A Practical Approach to Design, Implementation and Management (Addison Wesley, 2004) ISBN 0321210255
Howe D – Data Analysis for Database Design (Butterworth-Heinemann Ltd, 2001)ISBN 0750650869
Kroenke D – Database Concepts, 2nd Edition (Prentice Hall, 2004) ISBN 0131451413
Ritchie C – Relational Database Principles (Thomson Learning, 2002) ISBN 0826457134
Websites
www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
www.geekgirls.com/menu_databases.htm
www.smart-it-consulting.com/database/progress-database-design-guide/
Lecturer | |||
Campus | |||
Week | Session Update | Date | Signature |
10 |
Teaching and Learning Activities
The module tutor(s) will aim to combine lectures with tutorial activities. This environment will provide opportunities for the student to understand the course material through case study and text and to apply it in a practical way. The intent is to facilitate interactive class activities, and discussion about the significant role of research in a global and local business environment.
2.3 Teaching Ethos
The college’s approach towards teaching and learning is simple and effective. The main aim of UKCBC is to assist learners in maximising their potential by ensuring that they are taught clearly and effectively. This will enable students to engage in the learning environment and promote success in both their academic studies and subsequent career.
2.3.1 Methods of Delivery:
LECTURES:
These will be developed around the key concepts as mentioned in the indicative course content and will use a range of live examples and cases from business practice to demonstrate the application of theoretical concepts. This method is primarily used to identify and explain key aspects of the subject so that learners can utilise their private study time more effectively.
SEMINARS:
These are in addition to the lectures. The seminars are designed to give learners the opportunity to test their understanding of the material covered in the lectures and private study with the help of reference books. This methodology usually carries a set of questions identified in advance. Seminars are interactive sessions led by the learners. This method of study gives the learner an excellent opportunity to clarify any points of difficulty with the tutor and simultaneously develop their oral communication skills.
CASE STUDIES:
An important learning methodology is the extensive use of case studies. They enable learners to apply the concepts that they learn in their subjects. The learners have to study the case, analyse the facts presented and arrive at conclusions and recommendations. This assists in the assessment of the learner’s ability to apply to the real world the tools and techniques of analysis which they have learnt. The case study serves as a supplement to the theoretical knowledge imparted through the course work.
Plagiarism:
Any act of plagiarism will be seriously dealt with according to the colleges and awarding bodies’ regulations. In this context the definition and scope of plagiarism are presented below:
Plagiarism is presenting someone’s work as your won. It includes copying information directly from the web or books without referencing the material; submitting joint coursework as an individual effort; copying another student’s coursework; stealing coursework form another student and submitting it as your own work. Suspected plagiarism will be investigated and if found to have occurred will be dealt with according to the college procedure. (For further details please refer to the plagiarism policy and the student code of conduct.)
Assessment:
The module will be assessed meeting all the LO as specified by the awarding body, Ed Excel.
Please read the instructions carefully while addressing the tasks specified.
Contribution: 100% of the module
Outline Details: Individual report approx 4500 words. Details enclosed in the assignment brief.
ASSIGNMENT
Unit Number and title | Unit 17:Database Design Concepts |
Qualification | BTEC HND Computing and Systems Development (Level 4) |
Term Start Date | 05 June 2017 |
Assignment hand out date | |
Deadline/ Hand-in | 13 August 2017 |
Assignment title | Case Study of Shoengalleric Art Gallery |
Purpose of this assignment: To give learners opportunities to develop an understanding of the concepts and issues relating to databases and database design as well as the practical skills to translate that understanding into the design and creation of complex databases. | |
TASK 1 to TASK 3: You are required to follow the instructions as specified towards each task and support with research by using supportive materials like books, websites, etc., and give feedback on the findings by relating your arguments to the relevant case study as specified towards each task. Your report should fully follow the Harvard Referencing model. LO1 : Task – 1: The objective for this task is to address and exhibit understand databases and data management systems.You are required to carry out research by using supportive materials like books, websites, etc., and give a feedback of the findings on AC1.1 &1.2. The answers need to be reflected to the general Database Management Systems Concepts. Answers without application to an organization or without examples will be marked as Resubmission. (LO1 A.C 1.1 and A.C 1.2) Required:
| |
TASK 2: LO2- Task 2 Understand database design techniques The objective for this task is to address and exhibit understand database design techniques.You are required to carry out research by using supportive materials like books, websites, etc., and give a feedback of the findings on A.C 2.1 and A.C 2.2. The answers need to be reflecting the case study. Answers without application to an organization or without examples will be marked as Resubmission. Case Study Shoengalleric is an Art Gallery based in China which was established in 2010. Since then, the company has grown steadily and now has galleries in operation in the main cities in China. The Gallery has arts of different subjects. The Gallery fulfil the requirements of diverse clientele. They have a workforce of more than 500 staff operational in all the branches. However, the company is now so large that more and more administrative staff are being employed to cope with the ever-increasing amount of paperwork due to the outdated office based systems. Furthermore, the communication and sharing of information within the company is poor. The Director of Company, Mr. Peng Wang feels that too many errors are being made and that the success of his company will be short-lived if he does not do something to remedy the situation. He knows that a database could help in part to solve the problem and has approached you to help in creating a database application to support the running of the Shoengalleric Art Gallery operations. The Director, Mr. Peng Wang has provided the following hierarchy and brief description of how Shoengalleric Art Gallery operates: Director: Oversees the long terms goals for the gallery. Manager: Each Gallery has a Manager. The Manager is responsible for the day-to-day running of the gallery branch. Administrator: Involved in complete administration aspects like hiring personnel, managing contracts with various artists from the cities. Technician: Oversees all the technical requirements with respect to the installations of exhibitions i.e., oversees operation and documentation of art collections in the gallery. Working alongside him are the technical assistant supporting him in all the technical operations. Preparator: Involved in the preparations of the exhibitions in terms of installations and dismantling of the exhibition art works and also monitors environmental controls for the gallery. Gallery Assistant: Opens, closes and monitors the gallery and also involved in photography in the gallery. Booking Assistant: Involved in all the operations related to the bookings from the clients who visit the Gallery for the exhibition. The Gallery produces many exhibitions of the art work available in the Gallery. Artists provide the gallery with the installation instructions. All exhibitions are documented by the Gallery Assistant and a single page labelled diagram is created for each of the art work for the exhibition layout. Visitors visiting the gallery may be interested in buying the artwork so every piece of artwork has to be priced as per the instructions from the artist. Galleries keep information about artists, their names (which are unique), birthplaces, age and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds. For example, portraits, still lifes, works by Picasso, or works of the 19th century. A given piece may belong to more than one group. Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. Visitors who visit the Gallery need to make a booking in advance to see the resources available. For some events, the Gallery is open to all and they do not charge any fees for visits. For each customer, galleries keep that person’s unique name, address, total amount of Renminbi spent in the gallery (very important!), and the artists and groups of art that the customer tends to like. The gallery needs to store the information related to the upcoming exhibitions and displays in relevant files. You have been recruited by Shoengalleric Art Gallery to help them develop a database for supporting and effectively managing the operations of the Gallery. Your role as a Database Designer is to develop a Database Application by following the database developmental methodology. You should record all business assumptions that you made in the process. | |
(LO2 A.C 2.1 AND A.C 2.2) Required 2.1- Analyze a database developmental methodology for the given case study. 2.2- Discuss entity-relationship modelling and normalisation for the given case study. | |
TASK 3: LO3 Be able to design, create and document databases | |
LO3.( A.C 3.1., 3.2, 3.3 and 3.4) Required 3.1 – Apply the database developmental cycle to develop a database solution for the given case study. 3.2 – Design a fully functional database which includes tables, forms, queries, reports, various validation and verification techniques and various interaction in forms such as drop list , check box etc. by using a suitable database applications for the given case study. Your database design must contain minimum of four tables linked to your case study. 3.3 -Evaluate the effectiveness of the database solution and suggest methods of improvement for the given case study 3.4 - Provide supporting user and technical documentation of the database that you have implemented for the given case study. |
7 Evidence Checklist
Learning Outcomes | Assessment Criteria | In this assessment you will have the opportunity to present evidence that shows you are able to: | Task Number | Evidence Check list |
LO1 Understand databases and data management systems | 1.1 | Analyze the key issues and application of databases within organizational environments | 1 | |
1.2 | critically evaluate the features and advantages of database management systems | 1 | ||
LO2 Understand database design Techniques | 2.1 | analyse a database developmental methodology | 2 | |
2.2 | discuss entity-relationship modelling and normalisation | 2 | ||
LO3 Be able to design, create and document databases | 3.1 | apply the database developmental cycle to a given data set | 3 | |
3.2 | design a fully functional database (containing at least four inter-relational tables) including user interface | 3 | ||
3.3 | evaluate the effectiveness of the database solution and suggest methods of improvement | 3 | ||
3.4 | Provide supporting user and technical documentation. | 3 |
Merit Description: | |||
M1. Identify and apply strategies to find appropriate solutions. | Relevant theories and techniques have been application.
| To achieve M1, you will identify and apply strategies to find appropriate solutions to the given case study and use of relevant theories particular emphasis on (1.2), and techniques (2.1), (2.2). | |
M2. Select/ design and apply appropriate methods/ techniques. | A range of methods and techniques has been applied. | To achieve M2, the learner have apply appropriate methods and used suitable techniques with a particular focus on (3.3) (2.1), for the given case study. | |
M3. Present and communicate appropriate findings. | Appropriate structure and approach has been used. | To achieve M3 the leaner must present the work with appropriate structure and present and communicate the findings across the tasks 1 to 3 with special reference to selected organisation. Technical languages accurately used in all part of the assignment. | |
Distinction Description: | |||
D1. Use critical reflection to evaluate own work and justify valid conclusion. | Synthesis has been used to generate and justify valid conclusions | To achieve D1 you will have addressed the Criteria’s with valid conclusions, with special reference to task 2 & 3 justifying improvements and characteristics of success towards the database solution developed for the given company. Realistic improvements have been proposed for the solution implemented for the given case study. | |
D2. Take responsibility for managing and organizing activities. | Substantial activities/projects or investigations have been planned, managed and organized. | To achieve D2 you will have used substantial activities have been planned, managed and organized across your assignment with special reference to design and implementation of a database for the given case study. | |
D3. Demonstrate convergent, lateral and creative thinking | Ideas generate and decisions taken | To achieve D3 the learner expected to demonstrates new ideas or suggestions as improvements for the new database systems. The new database system also demonstrates creative thoughts. |
Use a standard report structure, Word-process the report and use Normal script of a proper font size 12.
Produce an academic report, detailing the above issues with a word limit of 4000 to 4500 words.
Complete the title page and sign the statement of authenticity.
The Assignment sheet should be attached in the front.
Submit the document in a folder in the form of a file as well as a soft copy on the submission date.
It should be uploaded on E-Learning platform before the deadline and submitted to -------- and sign the submission form of the college.
Assignments submitted after the deadline will not be accepted unless mitigating and may be entitled for a late fee.
Collusion and Plagiarism must be avoided.
Start each answer on a new page and pages should be numbered. Highlight each question clearly.
Include a reference list at the end of the assignment and use the Harvard referencing system.
All work should be comprehensively referenced and all sources must be fully acknowledged, such as books and journals, websites (include the date of visit), etc.
Try to give the page numbers, publishers' details and the year of publication
In order to pass you need to address all the LO and Assessment Criteria’s.
In order to get a merit you need to address all LO assessment criteria and the characteristics of M1, M2,M3
In order to get a Distinction you need to address all Merit characteristics and the characteristics of D1, D2 and D3. Use a common format for the questions, for example:
Introduction (analyse the question.)
Underpinning Knowledge (write about the relevant theory/points)
Applied knowledge(Data Analysis)
Conclusions and Recommendations (summarizing the whole scenario keeping in view pass, merit, distinction criteria.)
Account for | Give reasons for: explain why something happens. |
Analyse | Examine something in very close detail and from a number of angles. Identify the important points and chief features, and understand their relationships. |
Argue | Present a case for and against a proposal or statement and present your own opinion at the end. |
Compare | Show how two or more things are similar. |
Contrast | Look at two or more things and draw out differences. State whether the differences are significant. |
Critically evaluate | Weigh arguments for and against something, assess the strength of evidence on both sides. |
Define | Give the exact meaning of. |
Describe | Give a detailed account of the main features or characteristics ... |
Discuss | Write about the most important characteristics of something. Give arguments for and against, look at it from a variety of perspectives. |
Distinguish | Identify the differences between two items. |
Evaluate | Assess the worth or usefulness of something. Use evidence to support your opinion. |
Examine | Look at something in detail. You may be expected to 'critically evaluate' it as well. |
Explain | Make it clear why something happens or why it is the way it is. |
Identify | Recognise name and briefly describe something. |
Illustrate | Use examples to further explain or justify something. Could be visual or verbal. |
Interpret | Explain the meaning or significance of information or data that you are presenting. |
Justify | Provide evidence that supports an argument or idea. |
Outline | Give only the main points, show only the main structure. |
Prove | Present evidence in a logical structure to support an argument for a point of view. |
Relate (Two meanings) | 1. Show how ideas / theories/ events etc are linked or connected. 2. Tell a story. Explain something in a narrative fashion. |
Review | Survey and comment on the key aspects of something or a range of things. |
State | Give the main features clearly and simply. |
Summarise | Draw out the main points, omitting detail and examples. |
To what extent… | How far is something true, or contributes to a final outcome. Also how far is it not true? In academic writing the answer is usually somewhere in the middle. |
Trace | Describe the development of something; follow the order of different stages in an event or process. |