Database Management System for Car Company

database Management System for simple machine Comp bothSTRATEGY ANALYSIS description OF CURRENT BUSINESS ACTIVITIESpresentationDMC is a Dedicated cars familiarity that is specialized in simple machine gross revenue melodic line, and it was established Four old age ago in 7th May 2005, and its primary(prenominal) touch stands for Dedicated Motor Cars and its main division is in Tripoli Libya, it is a attach to with many high workers and hot employees working on it as they be sharing the same ambition, and always aiming for the vanquish in their military control, in both to improve their employees skills and in like manner to provide the best gos to their dear guest.DMC is rightfully working hard to achieve a raw(a) improvement in their art e really year, overly to sign up the approximately popular and successful cars to get them, and for the best prices to its customers, as they be alike filling their case show with a severe collection in to each(prenom inal) peerless month.As for this month they got a very promising collection of untested cars that b bely released this year, and old ones tho got good comments from millions of people who tried them, this collection result be for this year as in this m its the companys season clipping for the business.Current SystemThe current establishment of my company is going like this, each day we ar improving, and as I erect see now we are well liven as 1 of the top 20 in the flesh(predicate) companies in Libya, and we are in like manner known in the opposite continents as well.We are withal not restrict with hardly 1 nationality as we are offering jobs, because our company contains employees from distinguishable nationality as we see some of them in the main division and some other are in our different offices just about the globe.DMC has a good compute as over alone, and it is improving, likewise the customers are so pleased with our services in general, and our econom ical prices.That includes that we are always taking guardianship of our employees in terms of fee and to keep them improving by sending them to take courses in different sort of dramaturgys.Although we are doing a pretty good job in this business field and improving by a good rate percentage every(prenominal) year, however we are really cladding some of the major problems in our business, the main problem is that we are facing a overbold changes and new challenges in our business, similarly we put one over other competitors companies in the countries we are doing this business and change our cars and making our different services.Problem Background Effects of itSo our main problem actually includes semi problems that we should take sustenance of them so we lav exploit the main problem, which is we really need to move on with our services with the technology we are facing these days in doing those kind of business to direct it better, faster, and easier.And initiativ e of all is our own dust in the company, because it is still not well organized as an strong infrastructure and we similarly need a well studied design model for this infrastructure for our company.Thats w present I planned to made a Special entropybase system to our company, which go forth be including our employees, our daily customers for each season, and our gross revenue of cars on this season on as well covered by the posting air division of the company, and some peculiar(prenominal) selective information about our shipment topic and its fee and coverage services.The good thing is that with this new system we testament be able to remove a wrap up and direct relational amongst all the partitionings mentioned above and make a really strong infrastructure for the company confirming to be able to lineage information and data to a greater extent sufficiently.In this segment I would like to give you an idea about the goals and aims that we are aiming to achieve and t hey areImprove the business even much farther, to get ahead a new level of development.Reduce the paper work as we had a lost of some data from sequence to time.To always keep up with the other contenders companies so we wont lose our customers.Secure our data in a sufficient way.Provide a system where it is easy to get data for any of our customers.Improvement of action to satisfy the customers needs.Easy access to database and information utilize by employees.Remove barriers to widen its production by improving gross revenue procedure and reducing costs.Hardware and Software NecessityThese are all the hardware requirements needful Minimum Requirements Processor 2 Gigahertz (GHz) Pentium IV-Intel processor or Higher. Memory512 MB of RAM or Higher. Hard Disk 900 MB of available place required on the system drive, 4 gigabytes (GB) of available space capacity on the installation drive. Drive DVD-ROM or CD-ROM drive and Floppy drive Display super VGA (1024 x 768) or higher resol ution.These are all the software requirements neededMinimum Requirements Operating SystemsWindows XP Service Pack Final, Windows Vista, or Windows 7 any edition with MS-Office.H I S T O R YDMC Companys history is not that much as the company unless established ii years ago, but in those two years we can say that the company earned its rear and make various successful that led it to earn a respectful history account in the day it come ins doing business till our day now.Specification for New SystemThe main purpose from making this new application in my company is 1st to achieve the objectives that DMC is aiming to achieve, 2nd to make the business much easier for the customer and the employee as each of them ordain piss the profit of these kind of applications in our business. as well as the cars sales section (which is the Cars Details) will be much much attractive this way, and as it shows all the possible information that needed at the same time.That goes for the employee and customers dining tables as well they are helping the company over all by making it progressing even much faster and also to reduce the paper work. active Us table shows the company in a really short cut way, and its true words shows on this section, also for the producer of this application to grant his word on that section.Also the shipment tables is an important section and it is one of the reasons to determine whether a authoritative car sales company is really know about its business and can keep up, or sadly but have no idea about this field of business as it helps a lot in showing exactly the steps taking to ship your car and to not need to discuss any of the employee about it because it is very clear.On the other hand, last one but not last, is Plate Info table, which holds all the plate publication for the cars that have been purchased from our company, and also saving the summates from the other cars for the customers that are ding services for their own car.BUISN ESS ACTIVITIES OPERATIONSDESCRIPTION OF OPERATIONS Assign an Employee to handle a trustworthy(prenominal)(prenominal) invoice. tot the Country of the client to make the deliverance shipment service for his/her redact. Adding an Employee to the company. Assign a laughable ID to a customer. Adding a new Car to the showcase.Operation break Assign an Employee to handle a certain invoice.verbal description This outgrowth handles the fact that when we are having a new invoice table, we hand it over to a certain employee, and a (certain) employee way that he/she is not busy with another invoice or another cognitive operation in the moment.Input The assigned Employee must break through his/her ludicrous ID number, along with his name, and should be clear to read. return The Employee will be responsible for that invoice, and handle its full data.============================Operation summons Check the Country of the guest to make a delivery shipment service for his/her order. translation This operation handles the action when the customer wants to make a shipment service for his current order and to make it to a certain country.Input The customer must give information about the polish (Country) area where he/she wants to deliver the order.Output The Customer will be wedded an information about the shipment service such(prenominal) as the fee that known for that country.============================Operation severalise Adding an Employee to the company.Description As DMC expanding greatly in the bypast years, we are making this issue to a greater extent than than usual lately, and it is about recruiting a new employee to the company, further adding his information to the database.Input For this operation we need to have the new Employee ready, historic period, Nationality, and his/her learn of Birth to be able to add him/her to our database.Output The new Employee will be added to our database, and will be condition a laughable ID number to be known as one of DMC employees.============================Operation get Assign a preposterous ID to a Customer.Description This operation deals with having a new customer and need to be having his/her information in our database to be given an ID number for easily checking the customer status when he/she makes much than orders in the future.Input To make this operation successful, we need to have basic information yet helpful for us such as take a shit, Age, and Contact number.Output The Customer has been given a unique ID number.BUSINESS ACTIVITIESThis section describes the operations of DMC Company which is as I mentioned before but in more specific it deals with car sales that the company get from other retailers which recently built up a really strong relationship with DMC and are getting the best deals to expand our business.So in general DMC deals with customers with services, and we do car sale business, also we are very restrict with our system and the invoice we made are very clear for every car, further our employees are doing their best in every deal been made for each car.And more to mention here for our upcoming Database establishing is that we are going to build a new design system model that contains Cars, Employee, flier, consignment, and Customer.Those Five elements that I mentioned above will be our main concentrate on on this database design, as it is in general what DMC is is all about.For that matter here we are going to explain in details every Element we have for our Design Model, been explained by including its proportions and a abbreviated description about its main purpose.And every element will be known later on in the database description as Entity. card EntityThis entitys main purpose is to keep track of the sales, and for every deal that have been interchange by whom as an employee and for whom as a customer, also it will expose all the data necessary about the product (Car) AND ITS Unique ID, asset other major info rmation such as total price and the invoice reset number, whats more is this entity acts as the main entity colligate to all the other entities as it is also related to the ( encumbrance) entity as it also saves the shipment number in the invoice paper.The Attributes that have been made for this Entity are pecker_ issue, Customer_ID, Employee_ID, load_Nmber, Total_ equipment casualty, and Car_ID, and we will be talking about all the attributes for every entity in more details later on.Cars EntityThis entity deals with all the cars that the company DMC stores, and contains all its details such as the car brand name, its color and the car ID, and the shipment number for every specific shipment that been made, along with the most important attribute for all the customers which is the cars price.Employee EntityThis entity here will be busy of containing the Employee_ID, attend, Age, Nationality, and insure of Birth, it will be also including two separated types of employees and the y are Part clock time, and total beat.Customer EntityThis Entity is busy with giving the full information needed about the customers we are dealing with such as their Customer_ID, frame, Age, Contact_Number, and these information will be helpful in many facts in our company such as in making our invoices and assigning a specific car for a certain customer.Shipment EntityShipment entity is one of the services that made our company earn its name in such a short time, as this service allow a full shipment service for as much as needed for cars for our customers viewed with its price, and a unique Shipment_Number, also the Country attributes as it tells the terminal figure for each shipment services and the Fee attribute to tells the price for each country shown on the list.BUSINESS RULESIn this section we will be lean DMC companys main rules in its business, as for our customers for an example they are able to buy as much cars as they can with no any problems but for our cars in the stock we cannot offer to sell the car for more than one and just now one customer, also with our employees as any 1 of our employees can deal with more than just one car, but for the cars they should be dealt with only one employee to make the sell process.And as for these examples here we will be listing all the business rules for our company to make sure to be followed as it isFor every Shipment service that been made, it has to be assigned into an invoice that is related to it and that is a MUST. all(prenominal) Car in stock can have one invoice specialize for it same goes for the invoice as we made only one car can fit in each invoice rather than more than just one to make the business more flexible.every Car can have a shipment service depends on the customers need, and as for this shipment service, it can be done for more than just one car as well, but it should be made for at least one car for this service to be available.For every single customer, he/she can belong to m ore than just one invoice depends on the orders he/she made and how many cars been bought, and in the other hand for the invoice it can only belong to one and only one customer, means we cannot include two customers buying the same car model in one invoice because they sold the same car model.Every employee can handle more than just one invoice when making the selling process, he/she can serve more than just one invoice, but for the invoices, every invoice should be handled by only one employee.Also we have to make sure that we wont start the buying process for our customers unless we have their personal data in our database system.For our new employees, we cannot let them start working in active business unless the process of having their personal information inside our database is done. sign E.RDESIGN SPECIFICATION dishearten SPECIFICATIONInside DMC database I would like to take few modules and show the way to create them, and the purpose of making them, plus the reason of every column, so they can help me on the objectives Im aiming to achieve which they areDatabase DescriptionMy database consists of five tables (And two sub-type tables that will be mentioned later on) and they areCarsInvoiceCustomerEmployeeShipment control board Specification remit Name Invoice panel Description To be used as a annex of all the sales of the company.Attribute of the put back Invoice_Number, Customer_ID, Car_ID, Employee_ID, Shipment_Number, Total_Price. tabulate Type Independent. primeval key Invoice_Number.============================ send back Name Customer put back Description To be shown the customer basic information, along with his/her unique ID number.Attribute of the set back Customer_ID, Name, Age, Contact_Number.Table Type Independent primary election backbone Customer_ID============================Table Name EmployeeTable Description To be shown the Employee private data inside the database.Attribute of the Table Employee_ID, Name, Nationality, Date of Birt h.Table Type Independent (Supertype)Primary light upon Employee_ID============================Table Name CarsTable Description To be shown the Car basic data, along with the cars price and its shipment number.Attribute of the Table Brand_Name, Color, Car_ID, Shipment_Number, Price.Table Type IndependentPrimary Key Car_ID============================Table Name ShipmentTable Description To be shown more specific information about the shipment service, such as the countries that are available for the service, plus the fee.Attribute of the Table Shipment_Number, Country, FeeTable Type IndependentPrimary Key Shipment_Number============================Table Name exuberant TimeTable Description To be shown more specific information about the Full Time employees, such as the profits for the Full Time employees section, and the administration section which shows each full time employee and hi/her related administration division, along with the Employee ID.Attribute of the Table Employee_ID , Salary, and Administration.Table Type Independent (Subtype)Primary Key Employee_ID================================Table Name Part TimeTable Description To be shown more specific information about the Part Time employees, such as their Salary as well, and the Part time employees time shifting for their work time, and for sure their employee ID..Attribute of the Table Employee_ID, Salary, and Time Shifting.Table Type Independent (Subtype)Primary Key Employee_IDATTRIBUTE SPECEFICATION1. InvoiceEntity Name InvoicePrimary Key Invoice_NumberNameDatatype aloofness potpourri surrender vainConstraintsDescriptionInvoice_NumberInt artlessNoUniqueA certain unique numbers to keep track of each invoiceCustomer_IDVarchar(10) simpleNoUniqueThe ID for a customerEmployee_IDINT(10)simpletonNoUniqueThe unique ID for an employeeCar_IDVarchar(10)SimpleNoUniqueThe ID for a carTotal_PriceDoubleSimpleNoUniqueTotal price of a certain invoiceShipment_NumberInt(10)SimpleNoUniqueUnique number made for a cert ain shipment made2. CarsEntity Name CarsPrimary Key Car_IDNameDatatype aloofnessClassification yield NullConstraintsDescriptionBrand_NameVarChar(20)SimpleNoUniqueModel name of the carColorVarChar(10)SimpleNoUniqueColor of the carCar_IDVarchar(10)SimpleNoUniqueThe ID for a carShipment_NumberInt(20)SimpleNoUniqueUnique number made for a certain shipment madePriceDoubleSimpleNoUnique unquestionable Price of a car3. EmployeeEntity Name EmployeePrimary Key Employee_IDNameDatatypeLengthClassificationAllow NullConstraintsDescriptionEmployee_IDINT(10)SimpleNoUniqueThe unique ID for an employeeNameVarChar(20)SimpleNoUniqueThe first name of employeeNationalityVarchar(20)SimpleNoUniqueThe employees nationalityDate of BirthDateSimpleNoUniqueThe employees date of birth4. CustomerEntity Name CustomerPrimary Key Customer_IDNameDatatypeLengthClassificationAllow NullConstraintsDescriptionCustomer_IDVarchar(10)SimpleNoUniqueThe ID for a customerNameVarChar(10)SimpleNoUniqueThe first name of a custome rAgeInt(2)SimpleNoUniqueThe customer ageContact_NumberInt(10)SimpleNoUniqueCustomer Contact number5. ShipmentEntity Name ShipmentPrimary Key Shipment_NumberNameDatatypeLengthClassificationAllow NullConstraintsDescriptionShipment_NumberInt(20)SimpleNoUniqueUnique number made for a certain shipment madeCountryVarChar(10)SimpleNoUniqueThe country where the shipment destinationFeeInt(5)SimpleNoUniqueThe price of the shipment6. Full Time (Sub-Type Entity)Entity Name Full TimePrimary Key Employee_IDNameDatatypeLengthClassificationAllow NullConstraintsDescriptionEmployee_IDINT(10)SimpleNoUniqueThe unique ID for an employeeSalaryCurrencySimpleNoUniqueThe pay of a particular employeeAdministrationVarChar(20)SimpleNoUniqueThe certain task that the employee is responsible for7. Part Time (Sub-Type Entity)Entity Name Part TimePrimary Key Employee_IDNameDatatypeLengthClassificationAllow NullConstraintsDescriptionEmployee_IDINT(10)SimpleNoUniqueThe unique ID for an employeeSalaryVarChar(10)Simpl eNoUniqueThe salary of a particular employeeTime ShiftingDate/TimeSimpleNoUniqueThe certain day that the employee have his/her work shifts on cryptologyCREATING OF gameboardSCREATING OF TABLESAs for my companys project for our database I used Microsoft Office Access 2007, and I started creating tables on it.1 Cars TableScript Name Create Cars TableCREATE TABLE Cars(Car_ID VARCHAR PRIMARY KEY non NULL,Brand_Name VARCHAR(20) non NULL,Color VARCHAR(10) ,Shipment_Number whole number(20) not NULL,Price Double NOT NULL,)2 Invoice tableScript Name Create Invoice TableCREATE TABLE Invoice(Invoice_Number INTEGER PRIMARY KEY NOT NULL,Customer_ID VARCHAR(10) NOT NULL,Car_ID VARCHAR(10) NOT NULL,Shipment_Number INTEGER(10) NOT NULL,Price Double NOT NULL,)3 Customer tableScript Name Create Customer TableCREATE TABLE Customer(Customer_ID VARCHAR PRIMARY KEY NOT NULL,Name VARCHAR(10) NOT NULL,Age INTEGER(2) NOT NULL,Contact_Number INTEGER(10) NOT NULL,)4 Employee tableScript Name Create Employe e TableCREATE TABLE Employee(Employee_ID VARCHAR PRIMARY KEY NOT NULL,Name VARCHAR(20) NOT NULL,Nationality VARCHAR(20) NOT NULL,Date of Birth Date NOT NULL,)5 Shipment tableScript Name Create Shipment TableCREATE TABLE Shipment(Shipment_Number INTEGER PRIMARY KEY NOT NULL,Country VARCHAR(10) NOT NULL,Fee INTEGER(10) NOT NULL,)CONSTRAINTS SPECIFICATION Generalization hierarchy on an Employee.DescriptionThis hierarchy assigns the Employee entity as Supertype, and Full Time, Part Time as Subtypes, and since the both subtypes have many similarities, the Supertype have the attributes in common to the subtypes and they are Employee_ID, Name, Nationality, Date of Birth.. The subtypes cannot be one in another, means the Full Time employee cannot be a Part Time employee, and therefore this generalization hierarchy is of the type disjoint.Type DisjointSuper type Entity EmployeeSuper type Primary Key Employee_IDCommon Attributes Employee_ID, Name, Nationality, Date Of Birth.ConstraintsFull Ti me This subtype entity shows certain data about the Full time employees in the company and their related information which is needed to our database.As for the attributes for this entity Employee_ID, Salary, Administration.Part Time This subtype entity shows the data the other type of employees which is the part time employee type.As for the attributes for this entity Employe_ID, Salary, Time shifting,INITIAL POPULATING OF TABLESCars TablePopulating Table (Cars)INSERT INTO Cars (Brand_Name, Color, Car_ID, Shipment_Number, Price)VALUES (Mustang, Orange, 17111, 11333, 40000) Invoice TablePopulating Table (Invoice)INSERT INTO Invoice (Invoice_Number, Customer_ID, Car_ID, Employee_ID, Shipment_Number, Total_Price)VALUES (11, 9844, 17633, 1432, 11771, 25) Customer TablePopulating Table (Customer)INSERT INTO Customer (Customer_ID, Name, Age, Contact_Number)VALUES (9020, Avinash, 39, 2800131) Employee TablePopulating Table (Employee)INSERT INTO Employee (Employee_ID, Name, Nationality, Dat e of Birth)VALUES (1422, Vadimas, Libyan, 2/21/1986) Shipment TablePopulating Table (Shipment)INSERT INTO Shipment (Shipment_Number, Country, Fee)VALUES (11311, Germany, 130) Full Time TablePopulating Table (Full Time)INSERT INTO Full Time (Employee_ID, Salary, Administration)VALUES (1433, 1300, Secretary) Part Time TablePopulating Table (Part Time)INSERT INTO Part Time (Employee_ID, Salary, Time Shifting)VALUES (1441, 2000, 3/23/2009) USERS INPUT ( institution, DELETION, UPDATE)INSERTIONAdd New Car (Cars Table) motion name Insert (New Car in the cars showcase)INSERT INTO Cars (Brand_Name, Color, Car_ID, Shipment_Number, Price)VALUES (Type the Cars Brand, Type the Color of the Car , The Car ID, The Shipment Number related to this Car, The Price of this Car is)INSERTION 2Here we are going to try to insert a new data into the Shipment form, as we will be using it through the Shipment form insertion button included in that form.Then here the database will ask you if your up for insert ing new data into that formAnd here the data has been inserted successfully belowUPDATEUpdate New Customer (Customer Table)Query name Update (New Customer in the Customers Table)UPDATE Customer SET Contact_Number = Enter Customers phone numberWHERE Customer_ID = IDDELETIONDelete a Shipment Country (Shipment Table)DELETE *FROM ShipmentWHERE Country = Enter the Country that will be eliminatedPERFORMING QUERIES, SUB-QUERIESQUERY 1(Performing Alias technique into a Query)Description To manage all the Employees names into one interrogative sentenceQUERY 2(Performing Function technique into a Query)Description To ostentation all the cars available at the momentQUERY 3(Performing Concatenation technique into a Query)Description To give a brief description about each car in the showcaseQUERY 4(Performing Sub-Query technique into a Query)Description To Create a query from the employee data where it precaution only employees who are British.QUERY 5(Performing another Sub-Query technique i nto a Query)Description To Create a query from the Cars data where it have-to doe with only the minimum prices of listed cars plus be grouped by the cars brand name.TESTING bear witness case 1Objectives To use ALIAS in Employee Table riddle Data exact NAME AS FULLNAMEFROM Employee evaluate trial run forget To display the corresponding says. genuine Test Result As shown in the image below.Conclusion Success. The temperament was displayed.Test case 2Objectives To use ALIAS in Customer TableTest Data SELECT Name AS CustomerNameFROM Customer evaluate Test Result To display the corresponding magnetic disks.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 3Objectives To use ARITHMETIC in Car TableTest Data SELECT Car_ID, Brand_Name, (Price*2) AS Price with ShipmentFROM Cars anticipate Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 4Objectives To Create Shipment TableTest Data CREATE Table Shipment (Shipment_Number int (5),Country varchar (10),Fee money (10) ) Expected Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 5Objectives To use COMPARISON in Cars TableTest Data SELECT Car_ID, Brand_NameFROM CarsWHERE Price6000Expected Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 6Objectives To use COMPARISON in Shipment TableTest Data SELECT Shipment_Number, CountryFROM ShipmentWHERE FeeExpected Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 7Objectives To Create Cars tableTest Data CREATE Table Cars (Car_ID int (5),Shipment_Number int (5),Brand_Name varchar (10),Color va rchar (10),Price (10) ) Expected Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.TestDatabase Management System for Car CompanyDatabase Management System for Car CompanySTRATEGY ANALYSISDESCRIPTION OF CURRENT BUSINESS ACTIVITIESINTRODUCTIONDMC is a Dedicated cars company that is specialized in Car sales business, and it was established Four years ago in 7th May 2005, and its main name stands for Dedicated Motor Cars and its main division is in Tripoli Libya, it is a company with many high workers and skilled employees working on it as they are sharing the same ambition, and always aiming for the best in their business, in both to improve their employees skills and also to provide the best services to their dear customer.DMC is really working hard to achieve a new improvement in their business every year, also to get the most popular and successful cars to get them, and for the best pric es to its customers, as they are also filling their case show with a good collection in each month.As for this month they got a very promising collection of new cars that just released this year, and old ones but got well comments from millions of people who tried them, this collection will be for this year as in this time its the companys season time for the business.Current SystemThe current system of my company is going like this, each day we are improving, and as I can see now we are well known as 1 of the top 20 Private companies in Libya, and we are also known in the other continents as well.We are also not restrict with only 1 nationality as we are offering jobs, because our company contains employees from different nationality as we have some of them in the main division and some other are in our different offices around the globe.DMC has a good budget as overall, and it is improving, also the customers are so pleased with our services in general, and our economical prices.T hat includes that we are always taking care of our employees in terms of payment and to keep them improving by sending them to take courses in different kind of fields.Although we are doing a pretty good job in this business field and improving by a good rate percentage every year, but we are really facing some of the major problems in our business, the main problem is that we are facing a new changes and new challenges in our business, also we have other competitors companies in the countries we are doing this business and selling our cars and making our different services.Problem Background Effects of itSo our main problem actually includes semi problems that we should take care of them so we can solve the main problem, which is we really need to move on with our services with the technology we are facing these days in doing those kind of business to make it better, faster, and easier.And 1st of all is our own system in the company, because it is still not well organized as an st rong infrastructure and we also need a well studied design model for this infrastructure for our company.Thats where I planned to made a Special database system to our company, which will be including our employees, our daily customers for each season, and our sales of cars on this season on as well covered by the invoice section of the company, and some specific information about our shipment idea and its fee and coverage services.The good thing is that with this new system we will be able to make a clear and direct relational between all the sections mentioned above and make a really strong infrastructure for the company plus to be able to store information and data more sufficiently.In this segment I would like to give you an idea about the goals and aims that we are aiming to achieve and they areImprove the business even much further, to reach a new level of development.Reduce the paper work as we had a lost of some data from time to time.To always keep up with the other contend ers companies so we wont lose our customers.Secure our data in a sufficient way.Provide a system where it is easy to get data for any of our customers.Improvement of production to satisfy the customers needs.Easy access to database and information used by employees.Remove barriers to widen its production by improving sales procedure and reducing costs.Hardware and Software NecessityThese are all the hardware requirements needed Minimum Requirements Processor 2 Gigahertz (GHz) Pentium IV-Intel processor or Higher. Memory512 MB of RAM or Higher. Hard Disk 900 MB of available space required on the system drive, 4 gigabytes (GB) of available space capacity on the installation drive. Drive DVD-ROM or CD-ROM drive and Floppy drive Display Super VGA (1024 x 768) or higher resolution.These are all the software requirements neededMinimum Requirements Operating SystemsWindows XP Service Pack Final, Windows Vista, or Windows 7 any edition with MS-Office.H I S T O R YDMC Companys history is not that much as the company only established two years ago, but in those two years we can say that the company earned its name and make various successful that led it to earn a respectful history archive in the day it starts doing business till our day now.Specification for New SystemThe main purpose from making this new application in my company is 1st to achieve the objectives that DMC is aiming to achieve, 2nd to make the business much easier for the customer and the employee as each of them will have the advantage of these kind of applications in our business.Also the cars sales section (which is the Cars Details) will be much more attractive this way, and as it shows all the possible information that needed at the same time.That goes for the employee and customers tables as well they are helping the company over all by making it progressing even much faster and also to reduce the paper work.About Us table shows the company in a really short cut way, and its true words shows on th is section, also for the producer of this application to have his word on that section.Also the shipment tables is an important section and it is one of the reasons to determine whether a certain car sales company is really know about its business and can keep up, or sadly but have no idea about this field of business as it helps a lot in showing exactly the steps taking to ship your car and to not need to discuss any of the employee about it because it is very clear.On the other hand, last one but not last, is Plate Info table, which holds all the plate number for the cars that have been purchased from our company, and also saving the numbers from the other cars for the customers that are ding services for their own car.BUISNESS ACTIVITIES OPERATIONSDESCRIPTION OF OPERATIONS Assign an Employee to handle a certain invoice. Check the Country of the Customer to make the delivery shipment service for his/her order. Adding an Employee to the company. Assign a Unique ID to a Customer. A dding a new Car to the showcase.Operation Name Assign an Employee to handle a certain invoice.Description This operation handles the fact that when we are having a new invoice table, we hand it over to a certain employee, and a (certain) employee means that he/she is not busy with another invoice or another operation in the moment.Input The assigned Employee must appear his/her unique ID number, along with his name, and should be clear to read.Output The Employee will be responsible for that invoice, and handle its full data.============================Operation Name Check the Country of the Customer to make a delivery shipment service for his/her order.Description This operation handles the action when the Customer wants to make a shipment service for his current order and to make it to a certain country.Input The Customer must give information about the destination (Country) area where he/she wants to deliver the order.Output The Customer will be given an information about the shi pment service such as the fee that known for that country.============================Operation Name Adding an Employee to the company.Description As DMC expanding greatly in the past years, we are making this issue more than usual lately, and it is about recruiting a new employee to the company, further adding his information to the database.Input For this operation we need to have the new Employee Name, Age, Nationality, and his/her Date of Birth to be able to add him/her to our database.Output The new Employee will be added to our database, and will be given a Unique ID number to be known as one of DMC employees.============================Operation Name Assign a Unique ID to a Customer.Description This operation deals with having a new customer and need to be having his/her information in our database to be given an ID number for easily checking the customer status when he/she makes more orders in the future.Input To make this operation successful, we need to have basic informat ion yet helpful for us such as Name, Age, and Contact number.Output The Customer has been given a unique ID number.BUSINESS ACTIVITIESThis section describes the operations of DMC Company which is as I mentioned before but in more specific it deals with car sales that the company get from other retailers which recently built up a really strong relationship with DMC and are getting the best deals to expand our business.So mainly DMC deals with customers with services, and we do car sale business, also we are very restrict with our system and the invoice we made are very clear for every car, further our employees are doing their best in every deal been made for each car.And more to mention here for our upcoming Database building is that we are going to build a new design system model that contains Cars, Employee, Invoice, Shipment, and Customer.Those Five elements that I mentioned above will be our main focus on this database design, as it is in general what DMC is is all about.For th at matter here we are going to explain in details every Element we have for our Design Model, been explained by including its Attributes and a brief description about its main purpose.And every element will be known later on in the database description as Entity.Invoice EntityThis entitys main purpose is to keep track of the sales, and for every deal that have been sold by whom as an employee and for whom as a customer, also it will display all the data necessary about the product (Car) AND ITS Unique ID, plus other major information such as total price and the invoice reset number, whats more is this entity acts as the main entity related to all the other entities as it is also related to the (Shipment) entity as it also saves the shipment number in the invoice paper.The Attributes that have been made for this Entity are Invoice_Number, Customer_ID, Employee_ID, Shipment_Nmber, Total_Price, and Car_ID, and we will be talking about all the attributes for every entity in more details later on.Cars EntityThis entity deals with all the cars that the company DMC stores, and contains all its details such as the car brand name, its color and the car ID, and the shipment number for every specific shipment that been made, along with the most important attribute for all the customers which is the cars price.Employee EntityThis entity here will be busy of containing the Employee_ID, Name, Age, Nationality, and Date of Birth, it will be also including two separated types of employees and they are Part Time, and Full Time.Customer EntityThis Entity is busy with giving the full information needed about the customers we are dealing with such as their Customer_ID, Name, Age, Contact_Number, and these information will be helpful in many facts in our company such as in making our invoices and assigning a specific car for a certain customer.Shipment EntityShipment entity is one of the services that made our company earn its name in such a short time, as this service allow a ful l shipment service for as much as needed for cars for our customers viewed with its price, and a unique Shipment_Number, also the Country attributes as it tells the destination for each shipment services and the Fee attribute to tells the price for each country shown on the list.BUSINESS RULESIn this section we will be listing DMC companys main rules in its business, as for our customers for an example they are able to buy as much cars as they can with no any problems but for our cars in the stock we cannot offer to sell the car for more than one and only one customer, also with our employees as any 1 of our employees can deal with more than just one car, but for the cars they should be dealt with only one employee to make the sell process.And as for these examples here we will be listing all the business rules for our company to make sure to be followed as it isFor every Shipment service that been made, it has to be assigned into an invoice that is related to it and that is a MUST. Every Car in stock can have one invoice specialize for it same goes for the invoice as we made only one car can fit in each invoice rather than more than just one to make the business more flexible.Every Car can have a shipment service depends on the customers need, and as for this shipment service, it can be done for more than just one car as well, but it should be made for at least one car for this service to be available.For every single customer, he/she can belong to more than just one invoice depends on the orders he/she made and how many cars been bought, and in the other hand for the invoice it can only belong to one and only one customer, means we cannot include two customers buying the same car model in one invoice because they sold the same car model.Every employee can handle more than just one invoice when making the selling process, he/she can serve more than just one invoice, but for the invoices, every invoice should be handled by only one employee.Also we have to make sure that we wont start the buying process for our customers unless we have their personal data in our database system.For our new employees, we cannot let them start working in active business unless the process of having their personal information inside our database is done.INITIAL E.RDESIGN SPECIFICATIONTABLE SPECIFICATIONInside DMC database I would like to create few modules and show the way to create them, and the purpose of making them, plus the reason of every column, so they can help me on the objectives Im aiming to achieve which they areDatabase DescriptionMy database consists of five tables (And two sub-type tables that will be mentioned later on) and they areCarsInvoiceCustomerEmployeeShipmentTable SpecificationTable Name InvoiceTable Description To be used as a reference of all the sales of the company.Attribute of the Table Invoice_Number, Customer_ID, Car_ID, Employee_ID, Shipment_Number, Total_Price.Table Type Independent.Primary Key Invoice_Number.================ ============Table Name CustomerTable Description To be shown the customer basic information, along with his/her unique ID number.Attribute of the Table Customer_ID, Name, Age, Contact_Number.Table Type IndependentPrimary Key Customer_ID============================Table Name EmployeeTable Description To be shown the Employee private data inside the database.Attribute of the Table Employee_ID, Name, Nationality, Date of Birth.Table Type Independent (Supertype)Primary Key Employee_ID============================Table Name CarsTable Description To be shown the Car basic data, along with the cars price and its shipment number.Attribute of the Table Brand_Name, Color, Car_ID, Shipment_Number, Price.Table Type IndependentPrimary Key Car_ID============================Table Name ShipmentTable Description To be shown more specific information about the shipment service, such as the countries that are available for the service, plus the fee.Attribute of the Table Shipment_Number, Country, FeeTa ble Type IndependentPrimary Key Shipment_Number============================Table Name Full TimeTable Description To be shown more specific information about the Full Time employees, such as the Salary for the Full Time employees section, and the administration section which shows each full time employee and hi/her related administration division, along with the Employee ID.Attribute of the Table Employee_ID, Salary, and Administration.Table Type Independent (Subtype)Primary Key Employee_ID================================Table Name Part TimeTable Description To be shown more specific information about the Part Time employees, such as their Salary as well, and the Part time employees time shifting for their work time, and for sure their employee ID..Attribute of the Table Employee_ID, Salary, and Time Shifting.Table Type Independent (Subtype)Primary Key Employee_IDATTRIBUTE SPECEFICATION1. InvoiceEntity Name InvoicePrimary Key Invoice_NumberNameDatatypeLengthClassificationAllow NullCo nstraintsDescriptionInvoice_NumberIntSimpleNoUniqueA certain unique numbers to keep track of each invoiceCustomer_IDVarchar(10)SimpleNoUniqueThe ID for a customerEmployee_IDINT(10)SimpleNoUniqueThe unique ID for an employeeCar_IDVarchar(10)SimpleNoUniqueThe ID for a carTotal_PriceDoubleSimpleNoUniqueTotal price of a certain invoiceShipment_NumberInt(10)SimpleNoUniqueUnique number made for a certain shipment made2. CarsEntity Name CarsPrimary Key Car_IDNameDatatypeLengthClassificationAllow NullConstraintsDescriptionBrand_NameVarChar(20)SimpleNoUniqueModel name of the carColorVarChar(10)SimpleNoUniqueColor of the carCar_IDVarchar(10)SimpleNoUniqueThe ID for a carShipment_NumberInt(20)SimpleNoUniqueUnique number made for a certain shipment madePriceDoubleSimpleNoUniqueActual Price of a car3. EmployeeEntity Name EmployeePrimary Key Employee_IDNameDatatypeLengthClassificationAllow NullConstraintsDescriptionEmployee_IDINT(10)SimpleNoUniqueThe unique ID for an employeeNameVarChar(20)Simple NoUniqueThe first name of employeeNationalityVarchar(20)SimpleNoUniqueThe employees nationalityDate of BirthDateSimpleNoUniqueThe employees date of birth4. CustomerEntity Name CustomerPrimary Key Customer_IDNameDatatypeLengthClassificationAllow NullConstraintsDescriptionCustomer_IDVarchar(10)SimpleNoUniqueThe ID for a customerNameVarChar(10)SimpleNoUniqueThe first name of a customerAgeInt(2)SimpleNoUniqueThe customer ageContact_NumberInt(10)SimpleNoUniqueCustomer Contact number5. ShipmentEntity Name ShipmentPrimary Key Shipment_NumberNameDatatypeLengthClassificationAllow NullConstraintsDescriptionShipment_NumberInt(20)SimpleNoUniqueUnique number made for a certain shipment madeCountryVarChar(10)SimpleNoUniqueThe country where the shipment destinationFeeInt(5)SimpleNoUniqueThe price of the shipment6. Full Time (Sub-Type Entity)Entity Name Full TimePrimary Key Employee_IDNameDatatypeLengthClassificationAllow NullConstraintsDescriptionEmployee_IDINT(10)SimpleNoUniqueThe unique ID for a n employeeSalaryCurrencySimpleNoUniqueThe salary of a particular employeeAdministrationVarChar(20)SimpleNoUniqueThe certain task that the employee is responsible for7. Part Time (Sub-Type Entity)Entity Name Part TimePrimary Key Employee_IDNameDatatypeLengthClassificationAllow NullConstraintsDescriptionEmployee_IDINT(10)SimpleNoUniqueThe unique ID for an employeeSalaryVarChar(10)SimpleNoUniqueThe salary of a particular employeeTime ShiftingDate/TimeSimpleNoUniqueThe certain day that the employee have his/her work shifts onCODINGCREATING OF TABLESCREATING OF TABLESAs for my companys project for our database I used Microsoft Office Access 2007, and I started creating tables on it.1 Cars TableScript Name Create Cars TableCREATE TABLE Cars(Car_ID VARCHAR PRIMARY KEY NOT NULL,Brand_Name VARCHAR(20) NOT NULL,Color VARCHAR(10) ,Shipment_Number INTEGER(20) NOT NULL,Price Double NOT NULL,)2 Invoice tableScript Name Create Invoice TableCREATE TABLE Invoice(Invoice_Number INTEGER PRIMARY KEY NO T NULL,Customer_ID VARCHAR(10) NOT NULL,Car_ID VARCHAR(10) NOT NULL,Shipment_Number INTEGER(10) NOT NULL,Price Double NOT NULL,)3 Customer tableScript Name Create Customer TableCREATE TABLE Customer(Customer_ID VARCHAR PRIMARY KEY NOT NULL,Name VARCHAR(10) NOT NULL,Age INTEGER(2) NOT NULL,Contact_Number INTEGER(10) NOT NULL,)4 Employee tableScript Name Create Employee TableCREATE TABLE Employee(Employee_ID VARCHAR PRIMARY KEY NOT NULL,Name VARCHAR(20) NOT NULL,Nationality VARCHAR(20) NOT NULL,Date of Birth Date NOT NULL,)5 Shipment tableScript Name Create Shipment TableCREATE TABLE Shipment(Shipment_Number INTEGER PRIMARY KEY NOT NULL,Country VARCHAR(10) NOT NULL,Fee INTEGER(10) NOT NULL,)CONSTRAINTS SPECIFICATION Generalization hierarchy on an Employee.DescriptionThis hierarchy assigns the Employee entity as Supertype, and Full Time, Part Time as Subtypes, and since the both subtypes have many similarities, the Supertype have the attributes in common to the subtypes and they are Em ployee_ID, Name, Nationality, Date of Birth.. The subtypes cannot be one in another, means the Full Time employee cannot be a Part Time employee, and therefore this generalization hierarchy is of the type disjoint.Type DisjointSuper type Entity EmployeeSuper type Primary Key Employee_IDCommon Attributes Employee_ID, Name, Nationality, Date Of Birth.ConstraintsFull Time This subtype entity shows certain data about the Full time employees in the company and their related information which is needed to our database.As for the attributes for this entity Employee_ID, Salary, Administration.Part Time This subtype entity shows the data the other type of employees which is the part time employee type.As for the attributes for this entity Employe_ID, Salary, Time shifting,INITIAL POPULATING OF TABLESCars TablePopulating Table (Cars)INSERT INTO Cars (Brand_Name, Color, Car_ID, Shipment_Number, Price)VALUES (Mustang, Orange, 17111, 11333, 40000) Invoice TablePopulating Table (Invoice)INSERT IN TO Invoice (Invoice_Number, Customer_ID, Car_ID, Employee_ID, Shipment_Number, Total_Price)VALUES (11, 9844, 17633, 1432, 11771, 25) Customer TablePopulating Table (Customer)INSERT INTO Customer (Customer_ID, Name, Age, Contact_Number)VALUES (9020, Avinash, 39, 2800131) Employee TablePopulating Table (Employee)INSERT INTO Employee (Employee_ID, Name, Nationality, Date of Birth)VALUES (1422, Vadimas, Libyan, 2/21/1986) Shipment TablePopulating Table (Shipment)INSERT INTO Shipment (Shipment_Number, Country, Fee)VALUES (11311, Germany, 130) Full Time TablePopulating Table (Full Time)INSERT INTO Full Time (Employee_ID, Salary, Administration)VALUES (1433, 1300, Secretary) Part Time TablePopulating Table (Part Time)INSERT INTO Part Time (Employee_ID, Salary, Time Shifting)VALUES (1441, 2000, 3/23/2009) USERS INPUT (INSERTION, DELETION, UPDATE)INSERTIONAdd New Car (Cars Table)Query name Insert (New Car in the cars showcase)INSERT INTO Cars (Brand_Name, Color, Car_ID, Shipment_Number, Pri ce)VALUES (Type the Cars Brand, Type the Color of the Car , The Car ID, The Shipment Number related to this Car, The Price of this Car is)INSERTION 2Here we are going to try to insert a new data into the Shipment form, as we will be using it through the Shipment form insertion button included in that form.Then here the database will ask you if your up for inserting new data into that formAnd here the data has been inserted successfully belowUPDATEUpdate New Customer (Customer Table)Query name Update (New Customer in the Customers Table)UPDATE Customer SET Contact_Number = Enter Customers phone numberWHERE Customer_ID = IDDELETIONDelete a Shipment Country (Shipment Table)DELETE *FROM ShipmentWHERE Country = Enter the Country that will be eliminatedPERFORMING QUERIES, SUB-QUERIESQUERY 1(Performing Alias technique into a Query)Description To manage all the Employees names into one queryQUERY 2(Performing Function technique into a Query)Description To display all the cars available at t he momentQUERY 3(Performing Concatenation technique into a Query)Description To give a brief description about each car in the showcaseQUERY 4(Performing Sub-Query technique into a Query)Description To Create a query from the employee data where it concern only employees who are British.QUERY 5(Performing another Sub-Query technique into a Query)Description To Create a query from the Cars data where it concern only the minimum prices of listed cars plus be grouped by the cars brand name.TESTINGTest case 1Objectives To use ALIAS in Employee TableTest Data SELECT NAME AS FULLNAMEFROM EmployeeExpected Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 2Objectives To use ALIAS in Customer TableTest Data SELECT Name AS CustomerNameFROM CustomerExpected Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 3Objectives To use ARITHMETIC in Car TableTest Data SELECT Car_ID, Brand_Name, (Price*2) AS Price with ShipmentFROM CarsExpected Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 4Objectives To Create Shipment TableTest Data CREATE Table Shipment (Shipment_Number int (5),Country varchar (10),Fee money (10) ) Expected Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 5Objectives To use COMPARISON in Cars TableTest Data SELECT Car_ID, Brand_NameFROM CarsWHERE Price6000Expected Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 6Objectives To use COMPARISON in Shipment TableTest Data SELECT Shipment_Number, CountryFROM ShipmentWHERE FeeExpected T est Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test case 7Objectives To Create Cars tableTest Data CREATE Table Cars (Car_ID int (5),Shipment_Number int (5),Brand_Name varchar (10),Color varchar (10),Price (10) ) Expected Test Result To display the corresponding records.Actual Test Result As shown in the image below.Conclusion Success. The record was displayed.Test