Lucy

 Statement and Confirmation of Own Work Programmer /Qualification name:L5 Diploma in computing All NCC Education assessed assignments submitted by students must have this statement as the cover page or it will not be accepted for marking. Please ensure that this statement is either firmly attached to the cover of the assignment or electronically inserted into the front of the assignment. Student declaration I have read and understood NCC Education’s Policy on Academic Dishonesty and Plagiarism. To the almighty God and state mind that has enable me to come this far. My special dedication goes to my course Lecturer for his assistance in helping me understand the course and be able to carry my practical. I confirm the following details: Student ID/Registration number:- Name:LOKODO LUCY NAMETO Centre Name: COMPUTER PRIDE Module Name:DATABASE DESIGN AND DEVELOPMENT Module Leader: MR. JOHN JICHURU Number of words: 1,560 I confirm that this is my own work and that I have not plagiarized any part of it. I have also noted the assessment criteria and pass mark for assignments. Student Signature Submitted Date: 24/10/2014 Acknowledgement Special thanks to my lecturer whose constant advice and continued direction on project was outmost importance and indeed mandatory for successful completion of this project. Table of Contents Statement and Confirmation of Own Worki Student declarationi Acknowledgementiii INTRODUCTION1 TASK 12 Initial Design2 Entity relationship diagram model2 DATA DICTIONARY FOR THE ENTITY RELATIONSHIP MODEL2 Task two4 Data and Queries4 Implementation and data4 INPUT DATA IN EVERY TABLE6 Query10 Query showing services for a particular member.10 Task 312 Derived Data12 Task 414 Evaluation14 CONCLUSION15 BIBLIOGRAPHY16 INTRODUCTION This project involves Membership that allowed anyone who has made a professional sale of a work of fiction such as a short story or novel in the genre of science fictional based in UKSFWA. The member’s needs a database that can be able to keep record of their services, the publisher who published the book, and the of member who participate either full or associate. The tasks that follow show how this has been accomplished by creating the database. TASK 1 Initial Design Entity relationship diagram model DATA DICTIONARY FOR THE ENTITY RELATIONSHIP MODEL MEMBER TABLE ATTRIBUTE TYPE SIZE COMMENT MemberID PK INTEGER 5 This a primary key of the member and it identify a member writer MemberName VARCHAR 20 It describe member name MemberAddress VARCHAR 30 It show the address of the member MemberStartDate VARCHAR 20 It show the date that member TypeID FK INTEGER 5 This a foreign key it references in member type table MEMBER TYPE TABLE ATTRIBUTE TYPE SIZE COMMENT TypeID PK INTEGER 5 It describe the member type ID TypeName VARCHAR 10 It identify the member type name PUBLICATION TABLE ATTRIBUTE TYPE SIZE COMMENT PublicationNo PK INTEGER 5 It identify the number that was published MemberID FK INTEGER 5 Foreign key it references in member table Title VARCHAR 25 A the book is given title Dateofpublication VARCHAR 25 It show the date of publication Publisher VARCHAR 20 It identify a person who published a book FictionID FK INTEGER 5 The foreign key it references in type of fiction table TYPE OF FICTION TABLE ATTRIBUTE TYPE SIZE COMMENT FictionID PK INTEGER 5 This the primary key of type of fiction table FictionName VARCHAR 15 It identify the name of the fiction SUBSCRIBER SERVICES TABLE ATTRIBUTE TYPE SIZE COMMENT MemberID PK FK INTEGER 5 This is the foreign key it references in member table ServicesID PK FK VARCHAR 30 The foreign key it references in services table SERVICES TABLE ATTRIBUTE TYPE SIZE COMMENT ServicesID PK INTEGER 5 The services identification and it a primary key ServicesName VARCHAR 30 It identify the services name Task two Data and Queries Implementation and data Member table Create table member(member_ID int(5) primary key, member _Name varchar(30), Member_Address Varchar(30), member_StartDate varchar(20), type_ID int(5) foreign key(type_ID) references type(type_ID)); Membertype table Create table membertype(type_Name int(5) primary key, Type_Name varchar(10); Type of fiction table Create table typeoffiction(fiction_ID int(5) primary key, fiction_Name varchar(15)); Subscriberservices table create table subscriberservices (member_ID int(5), services_ID int(5), primary key(member_ID,services_ID), foreign key(member_ID) references member(member_ID), foreign key(services_ID) references services(services_ID)); Publication table create table publication(publication_No int(5) primary key, member_ID int(5), foreign key(member_ID)references member(member_ID), typeoffiction varchar(15), title varchar(20), dateofpublication varchar(25), publisher varchar(20), fiction_ID int(5), foreign key(fiction_ID) references fiction (fiction_ID)); Services table Create table services(services_ID int(5) primary key, Services_Name varchar(30)); INPUT DATA IN EVERY TABLE Member table Insert into member values(78, “Arnold Layne”, “23 The Elms, Enfield, Middlesex”, "1st January 2014" ,”01”); Insert into member values(87, “K Madison Smith”, “c/o Rapid House Gardens, Essex”, "1st December 2015",”02”); Insert into member values(66, “Emily Floyd”, “Old Nun's head, SE15”, "1st December 2015",”01”); Insert into member values(63, "Anjelo lokiru", "Magwi, head street", "2nd May 2012",”02”); Insert into member values(88, "Lucy Amoo", "Juja street 17", "3rd November 2008",”01”); Insert into member values(90, "Samuel Wawesh", "Muranga, state 45", "1st July 2005",”01”); Insert into member values(89, "John Wainaina", "Kiambu, Green House", "1st July 2015",”02”); Insert into member values(75, "Mary Naboi", "Juba EES 15", "2nd November 2014",”01”); Insert into member values(67, "Lucy Lokodo", "chukudum NPA", "2nd June 2013",”02”); Services table Insert into services values(01, "Acorn Travel Insurance"); Insert into services values(02, "Sage Accounting software"); Insert into services values(03, "National Trust Discount"); Insert into services values(04, "Ms Office Discount"); Insert into services values(05,"Thomas Cook Holiday Voucher"); Subscriberservices table Insert into subscriberservices values(78, "01"); Insert into subscriberservices values(78, "02"); Insert into subscriberservices values(78, "03"); Insert into subscriberservices values(87, "02"); Insert into subscriberservices values(66, "04"); Insert into subscriberservices values(66, "02"); Insert into subscriberservices values(66, "05"); Membertype table Insert into membertype values(01,"Full"); Insert into membertype values(02,"Associate"); Type of fiction table Insert into typeoffiction values(01, "Novel"); Insert into typeoffiction values(02, "Short Story"); Publication table Insert into publication values(10, "78", "The Sand of New World", "1988", "penguin", "1"); Insert into publication values(20, "87", "Under the River", "2011", "inerzone Magazine", "2"); Insert into publication values(30, "87", "Eclectic Blue", "2011", "inerzone Magazine", "2"); Insert into publication values(40, "87", "Rescue", "2011", "Jupiter Magazine", "2"); Insert into publication values(50, "66", "Egor Svoma", "1999", "oxford", "1"); Insert into publication values(60, "63", "Across the River", "1987", "Magazine writer", "2"); Insert into publication values(70, "88", "Alchemist", "1990", "paulo", "1"); Insert into publication values(80, "90", "haft a day", "1970", "By Chembe", "2"); Insert into publication values(90, "89", "River Fall a Side", "2008", "Johnothan", "1"); Insert into publication values(100, "75", "Man of the people", "1985", "William", "2"); Insert into publication values(110, "67", "Valley of darkness", "1982", "Jame", "1"); Query Query showing services for a particular member. a) SELECT member.member_ID, member.member_Name, services.services_Name FROM member INNER JOIN (services INNER JOIN subscriberservices ON services.services_ID = subscriberservices.services_ID) ON member.member_ID = subscriberservices.member_ID WHERE (((member.member_ID)=78)); b) Selects all the associate members SELECT member.member_Name, memberType.type_Name FROM memberType INNER JOIN member ON memberType.type_ID = member.type_ID WHERE (((memberType.type_Name)="Associate")); c) Selects all the novels that have been used as member validation. SELECT p.publication_No,p.title, m.member_ID, m.member_Name, f.fiction_name FROM membertype mt INNER JOIN member m on mt.type_id = m.type_id INNER JOIN publication p on m.member_id=p.member_id INNER JOIN typeoffiction f ON p.fiction_id=f.fiction_id WHERE f.fiction_name = 'Novel'; d) Shows how many short stories have been used as validation by full members. SELECT p.publication_No,p.title, m.member_ID, m.member_Name, f.fiction_name, mt.type_Name FROM membertype mt INNER JOIN member m on mt.type_id = m.type_id INNER JOIN publication p on m.member_id=p.member_id INNER JOIN typeoffiction f ON p.fiction_id=f.fiction_id WHERE f.fiction_name = 'short story' AND mt.type_name = 'Full'; Task 3 Derived Data A table in application data model can have derived columns for which values are computed base on the values of other table columns. In this case two tables will be affected. Membertype table and member table First alter table membertype so that addition column will be added to membertpe table. By using alter table membertype add typecharges float; then the typecharges will be added into the table. Secondly is to update the data into the membertype table specifically in column called typecharges then the value is sign. The full member will be given 7 pound and associate member is given 3 pound. To update table we use this update membertype set type charges=7.00 where type _ID =1; update membertype set type charges=3.00 where type _ID =2; type_ID is use to get the membertype whether is full or associate but here 1 is use to get a full member while 2 is use to get associate member. Thirdly the aggregate that have applied here is count. Count is use to returns number of values in a column therefore I use 1st January 2014 and 2nd November 2014 to get the total values of each full and associate member and the membertype will change to membercategory then typecharges change to totalfees the formulae to get all these is to SELECT membertype.type_Name AS MEMBERCATEGORY,(COUNT(Member.type_ID) * membertype.typecharges) AS TOTALFEES FROM membertype INNER JOIN Member ON membertype.type_ID = Member.type_ID WHERE member.member_StartDate BETWEEN '1st January 2014' and '2nd November 2014' GROUP BY membertype.type_Name, membertype.typecharges; Alter table membertype Add typecharges float; Update membertype Set typecharges=7.00 Where type_ID=1; update membertype set typecharges=3.00 where type_ID=2; SELECT membertype.type_Name AS MEMBERCATEGORY, (COUNT(Member.type_ID) * membertype.typecharges) AS TOTALFEES FROM membertype INNER JOIN Member ON membertype.type_ID = Member.type_ID WHERE member.member_StartDate BETWEEN '1st January 2014' and '2nd November 2014' GROUP BY membertype.type_Name, membertype.typecharges; Task 4 Evaluation The database that I have created has met the requirements of the firm in different ways. Firstly, it can keep records of every member and the services that they requested e.g. Sage accounting. The system has data integrity since it won’t allow alteration of data in the records kept in the database. The next thing is that the database avoids redundancy by the use of primary keys hence avoiding data duplication. That is, one member_ID can only belong to one member; it can’t be allocated to another member because doing so will generate errors. Finally, using a database will be more secure to the firm since it provides other means for backup as compared to the paper records they keep. This method is also secure to physical calamities like records getting lost, being torn or even burnt. The firm will create more working space they used for since the files records will become obsolete. And it also help for future reference incase of lost of the data in the company or anywhere else where people using the databases to keep their records. THE TABLE BELOW SHOW THE REQUIREMENTS MET Requirements Whether requirements met Storage It easy to stored member and also to categorize members Data Manipulation Easy to insert, update, select and delete table information Data redundancy It reduces data duplication Security It well protected no interference with outsiders members Data integrity It used for setting up rules in database only members who are in database can access the data CONCLUSION After the completion of the project, I have learnt that it is easy to use the database and more convenient on storing data. It saves on time when searching for particular records rather than searching them manually in files. The system I have created has met the needs of the firm in the ways it need to be in the project. I also learnt that this system is healthier for future used the only things you need to do is to keep the system in a better and secure place whereby no one should interfere with your system then your data can take many year without losing them. BIBLIOGRAPHY Level 5 diploma in Business Information Technology (L5BIT). NCC Education Limited, the towers, towers Business Park, Wilmslow Road, Didsbury, Manchester M20 2EZ, UK. © NCC Education Limited.