程序需求分析个人学习参考作业题目

程序需求分析个人学习参考作业题目BigLeader'sMissionandObjectives:BigLeader'smissionisto"bringdigitaltoeveryperson,home,andorganizationforafullyconnected,intelligentworld."Theirmainobjectivesincludetechnologicalinnovation,providinghigh-qualityproductsandservices,andfosteringasustainableandinclusivedigitalecosystem.MainFunctionalityoftheDatabaseSystem:OrderManagement:Thedatabasewillfacilitatetheprocessofordermanagement,allowingBigLeadertotrackcustomerorders,manageorderstatus,andprocesspaymentsefficiently.InventoryManagement:InventorydatawillhelpBigLeadermonitorstocklevels,forecastdemand,andensuretimelyreplenishmentofproducts.CustomerRelationshipManagement(CRM):ThedatabasewillenableBigLeadertomaintaincustomerprofiles,purchasehistory,andinteractions,helpingimprovecustomerserviceandmarketingefforts.SupplierManagement:Storingsupplierdetailswillaidinmanagingrelationshipswithvendors,trackingpurchases,andnegotiatingcontracts.EmployeeRecordsandHRManagement:Thedatabasewillcentralizeemployeeinformation,supportingHRactivitiessuchaspayroll,performanceevaluation,andworkforceplanning.SalesAnalysis:Byanalyzingsalesdata,BigLeadercanidentifytrends,popularproducts,andcustomerpreferencestomakeinformedbusinessdecisions.FinancialTrackingandExpenseManagement:Thedatabasewillhelpmonitorexpenses,trackfinancialtransactions,andmaintainaccuratefinancialrecords.ShipmentTracking:ShipmentdetailswillenableBigLeadertomonitorthedeliverystatusandprovidetimelyupdatestocustomers.DataReportingandAnalysis:Thedatabasesystemwillsupportgeneratingreportsandconductingdataanalysistogaininsightsintovariousaspectsofthebusiness.SecurityandAccessControl:Implementingrobustsecuritymeasureswillsafeguardsensitivedataandcontrolaccesstothedatabasetoensuredataprivacyandcompliance.1、 EntitiesandRelationshipsintheDatabase:Customers-ThisentityrepresentstheindividualsororganizationsthatpurchaseBigLeaderproductsorservices.Itstorescustomerinformationsuchasname,contactdetails,andpurchasehistory.Products-ThisentitycontainsdetailsaboutthevariousproductsofferedbyBigLeader,includingproductnames,descriptions,specifications,andprices.Orders-Thisentityrecordsinformationaboutcustomerorders,includingtheproductspurchased,orderdate,paymentdetails,andorderstatus.Suppliers-ThisentitystoresinformationaboutthesuppliersfromwhomBigLeadersourcescomponentsandmaterialsforitsproducts.Employees-ThisentityrepresentsBigLeader'sworkforceandincludesdetailssuchasemployeenames,contactinformation,jobroles,anddepartmentinformation.Inventory-ThisentitytracksthestocklevelsofBigLeader'sproducts,helpingthecompanymanageitssupplychainandensureproductavailability.Sales-Thisentitycapturessales-relateddata,includingtransactiondetails,quantitiessold,revenuegenerated,andassociatedcustomerandproductinformation.Payments-Thisentityrecordspaymentdetailsforcompletedorders,includingpaymentmethods,transactionIDs,andpaymentdates.Shipment-Thisentitytracksshipmentdetails,suchastrackingnumbers,deliverydates,andtheassociatedorderinformation.2、Customers-Orders:Onecustomercanplacemultipleordersovertime.Eachorderisassociatedwithonespecificcustomer.Thisisaone-to-manyrelationship.Customers-Payments:Acustomercanmakemultiplepaymentsfordifferentorders.Eachpaymentislinkedtoonespecificcustomer.Thisisaone-to-manyrelationship.Products-Inventory:Eachproductistrackedintheinventorytomanagestocklevels.Theinventoryentitystoresthequantityofeachproductavailable.Thisisaone-to-oneorone-to-manyrelationship,dependingonhowtheinventoryismanaged.Products-Orders:Eachordercancontainmultipleproducts.Eachproductcanbeapartofmultipleorders.Thisisamany-to-manyrelationship,typicallyimplementedusinganintermediatejunctiontable.Orders-Sales:Eachordergeneratesasalesrecord,representingacompletedtransaction.Thesalesrecordcontainsdetailsliketheorderdate,quantitysold,andrevenuegenerated.Thisisaone-to-onerelationship.Orders-Shipment:Eachordermayhaveacorrespondingshipmentfordelivery.Theshipmententitytracksdeliveryinformationliketrackingnumbersanddeliverydates.Thisisaone-to-onerelationship.Employees-Orders:Employeesmayberesponsibleforhandlingcustomerordersorprocessingthem.Eachordercanbeassignedtooneormoreemployees.Thisisamany-to-manyrelationship,typicallyimplementedusinganintermediatejunctiontable.Suppliers-Products:EachsuppliermayprovidemultipleproductstoBigLeader.Eachproductcanbesourcedfromonespecificsupplier.Thisisaone-to-manyrelationship.3、4、Customers(Customer_IDPK,Name,ContactInfo,PurchaseDate)Products(Product_IDPK,Name,Description,Specifications,Price)Orders(Order_IDPK,OrderDate,Payment_IDFK,Status,Customer_IDFK)Suppliers(Supplier_IDPK,Name,ContactInfo)Employees(Employee_IDPK,Name,ContactInfo,JobRole,Department_IDFK)Inventory(Product_IDPK,StockLevel)Sales(Payment_IDPK,Amount,Transaction_ID)Payments(Payment_IDPK,PaymentMethod,TransactionID,PaymentDate)Shipment(Order_IDPK,TrackingNumber,DeliveryDate)5、FirstNormalForm(1NF):Eachtablehasaprimarykey(PK)thatuniquelyidentifieseachrecord.Allattributesareatomic,meaningtheycannotbefurtherdivided.SecondNormalForm(2NF):TheOrderstablehasacompositeprimarykey(Order_ID,Customer_ID).WecanremovetheCustomer_IDfromtheOrderstableandcreateaseparatetableforCustomer_Orderrelationshiptoresolvepartialdependency.Customer_Order(Customer_IDPK,Order_IDPK)TheSalestablehasacompositeprimarykey(Payment_ID,Amount).WecanremovetheAmountfromtheSalestableandcreateaseparatetableforPayment_Salerelationshiptoresolvepartialdependency.Payment_Sale(Payment_IDPK,Transaction_ID)ThirdNormalForm(3NF):TheOrderstablehasatransitivedependencyontheCustomer_IDattribute,asOrderDateandStatusdependonCustomer_ID.WecancreateaseparatetableforCustomerdetailstoresolvethis.Customers(Customer_IDPK,Name,ContactInfo,PurchaseDate)TheOrderstablehasatransitivedependencyonthePayment_IDattribute,asOrderDateandStatusdependonPayment_ID.WecancreateaseparatetableforPaymentdetailstoresolvethis.Payments(Payment_IDPK,Amount)6、CREATETABLECustomers(Customer_IDINTPRIMARYKEY,NameVARCHAR(100),ContactInfoVARCHAR(100),PurchaseDateDATE);CREATETABLEProducts(Product_IDINTPRIMARYKEY,NameVARCHAR(100),DescriptionVARCHAR(255),SpecificationsTEXT,PriceDECIMAL(10,2));CREATETABLEOrders(Order_IDINTPRIMARYKEY,OrderDateDATE,StatusVARCHAR(50),Payment_IDINT,Customer_IDINT,FOREIGNKEY(Payment_ID)REFERENCESPayments(Payment_ID),FOREIGNKEY(Customer_ID)REFERENCESCustomers(Customer_ID));CREATETABLESuppliers(Supplier_IDINTPRIMARYKEY,NameVARCHAR(100),ContactInfoVARCHAR(100));CREATETABLEEmployees(Employee_IDINTPRIMARYKEY,NameVARCHAR(100),ContactInfoVARCHAR(100),JobRoleVARCHAR(100),Department_IDINT,FOREIGNKEY(Department_ID)REFERENCESDepartments(Department_ID));CREATETABLEInventory(Product_IDINTPRIMARYKEY,StockLevelINT);CREATETABLESales(Payment_IDINTPRIMARYKEY,Transaction_IDINT,AmountDECIMAL(10,2),FOREIGNKEY(Payment_ID)REFERENCESPayments(Payment_ID));CREATETABLEPayments(Payment_IDINTPRIMARYKEY,PaymentMethodVARCHAR(100),TransactionIDINT,PaymentDateDATE);CREATETABLEShipment(Order_IDINTPRIMARYKEY,TrackingNumberVARCHAR(100),DeliveryDateDATE,FOREIGNKEY(Order_ID)REFERENCESOrders(Order_ID));CREATETABLEExpenses(Expense_IDINTPRIMARYKEY,TypeVARCHAR(100),DateDATE);INSERTINTOCustomers(Customer_ID,Name,ContactInfo,PurchaseDate)VALUES(1,'JohnDoe','john@','2023-07-10'),(2,'JaneSmith','jane@','2023-07-12');INSERTINTOProducts(Product_ID,Name,Description,Specifications,Price)VALUES(1,'Smartphone','High-performancesmartphone','5G,128GBstorage',699.99),(2,'Laptop','Powerfullaptopforworkandgaming','16GBRAM,512GBSSD',1299.99);INSERTINTOOrders(Order_ID,OrderDate,Payment_ID,Status,Customer_ID)VALUES(101,'2023-07-15',1,'Processing',1),(102,'2023-07-16',2,'Shipped',2);INSERTINTOSuppliers(Supplier_ID,Name,ContactInfo)VALUES(1,'SupplierA','supplierA@'),(2,'SupplierB','supplierB@');INSERTINTOEmployees(Employee_ID,Name,ContactInfo,JobRole,Department_ID)VALUES(1,'AliceJohnson','alice@','SalesRepresentative',1),(2,'BobSmith','bob@','WarehouseManager',2);INSERTINTOInventory(Product_ID,StockLevel)VALUES(1,100),(2,50);INSERTINTOSales(Payment_ID,Amount,Transaction_ID)VALUES(1,699.99,1001),(2,1299.99,1002);INSERTINTOPayments(Payment_ID,PaymentMethod,TransactionID,PaymentDate)VALUES(1,'CreditCard','1234-5678-9012-3456','2023-07-16'),(2,'PayPal','PAY-ABCDEF123456','2023-07-17');INSERTINTOShipment(Order_ID,TrackingNumber,DeliveryDate)VALUES(101,'TRK-123456','2023-07-18'),(102,'TRK-987654','2023-07-19');INSERTINTOExpenses(Expense_ID,Type,Date)VALUES(1,'OfficeSupplies','2023-07-10'),(2,'TravelExpenses','2023-07-15');Retrieveallcustomers:SELECT*FROMCustomers;Getallproducts:SELECT*FROMProducts;7、1:RetrieveallcustomerinformationSELECT*FROMCustomers;2:GetdetailsofaspecificproductbyProduct_IDSELECT*FROMProductsWHEREProduct_ID=1;3:ShowallorderswiththeirorderstatusSELECTOrder_ID,StatusFROMOrders;4:ListallsuppliersandtheircontactinformationSELECT*FROMSuppliers;5:FindemployeesworkingintheSalesdepartmentSELECT*FROMEmployeesWHEREJobRole='SalesRepresentative';6:CheckthecurrentstocklevelforaspecificproductSELECTStockLevelFROMInventoryWHEREProduct_ID=1;7:CalculatetotalrevenuefromsalesSELECTSUM(Amount)ASTotalRevenueFROMSales;8:FindpaymentdetailsforaspecificorderSELECTPaymentMethod,TransactionID,PaymentDateFROMPaymentsWHEREPayment_ID=1;9:RetrieveshipmentinformationforanorderSELECTTrackingNumber,DeliveryDateFROMShipmentWHEREOrder_ID=101;10:Sho