Description
Design of the Relational Database Structure
Submit the following information in a Word document:
Create a table(s) to describe current and historical rides;
Write SQL statements that would perform the following tasks:
Compute the average ride duration for a given user;
Compute the number of bikes checked out at the given bike station;
Retrieve the list of all currently active users.
Make sure to use the appropriate DML and DDL commands.
In the comments field, please provide a text document of code with proper syntax.1
Course Project
2
Bike Renting
Bike renting is one of the operational business processes for the bike-sharing system.
Firstly, the group should ensure that they have a web page and mobile app that provide details of
the location of docking stations, bike types, availability of the bikes, and rent costs. The
customer will initiate the booking and ordering process. Therefore, the customer will need to
have the mobile application installed on their phone and have a registered account with the
group. After viewing the nearest docking stations, users can pre-book a bike or order to unlock it
once they get to the location. For pre-booking, the customers will choose the preferred docking
station where they will pick the bike, pick time, and the type of bike they need. The IS system
will save the information and lock the bike until the user picks it up. However, if a customer
delays to pick the pre-booked bike by 15 minutes, the bike will be available to other users.
In the second option, a user will go to the docking station, select a bike, then use their
phone app to scan a QR code on the bike that unlocks it. The information system will play a
significant role in the process. It will aid in updating information on the available bikes and their
location. Also, the system will help with pre-booking bikes and unlocking them. Thus, an
automated system will be utilized, eliminating labor costs.
User Management
User management will entail managing customer accounts and keeping track of customer
engagement. The first step is to ensure there is an ease in registration. Next, user engagement
will be crucial as it will help the group identify areas that need improvements and those that meet
clients’ tastes and preferences. Feedback helps in continued growth, decision-making, and
comparing with competitors (Nasr et al, 2014). Thus, the user management process should
ensure that customers are engaged to provide feedback.
3
The system should start the process and prompt the customer to comment on their
experience and rate the service after returning the bike. Also, the group can conduct random
surveys at regular intervals where they send questionnaires to customers. The information
received from the surveys will also help improve user management. The information system will
support the process by prompting customers to give service reviews and randomly selecting
customers who receive the questionnaires. Moreso, it will keep records of the users, personal
data like email and age and monitor the use of the bike rental services per user.
Billing
The billing process will occur after a bike return. The process will be system initiated to
improve efficiency. Thus, once a customer returns a bike to a docking station, the mobile app
will request them to pay. The amount applicable is subject to the time a user had the bike and if
there are fines. The group should provide various payment methods that customers can choose
from as it eases checkout and convenience (Koksal, 2019). Partnerships with mobile payment
apps can aid in promoting a cashless and seamless transaction. Cash and card payments can be
made at the stations to provide users with alternatives. The billing process begins upon booking a
bike or unlocking it from the docking station. At that point, the user should have an annual
subscription or a day pass. The information system will support the billing process in various
ways.
First, it will keep records of customers who have subscriptions and those who need a day
pass. Second, the system will track when a bike was unlocked and when it was returned. The
information will be used to calculate the amount that a customer will pay. Also, the system will
provide the customer with a bill breakdown, including the duration of riding and any extra
charges that apply. All the payment methods will be system-supported, ensuring updates are
4
made and thank you messages sent once the user clears the bill. Ultimately, the information
system will significantly improve the process and promote customer satisfaction.
IS Architecture and Rationalen
Architecture is one of the significant aspects when designing an information system for a
bike-sharing system. Although the group can adopt different architectural models, a
decentralized model is best. The model can be integrated with a Customer Relationship
Management (CRM) system to provide holistic experiences for the users and owners of the
system. According to Guo et al. (2018), most bike-sharing systems are centralized, which has led
to increased vandalism and leak of user information. The leakage is associated with the risks and
exposure of the server in a centralized system. Therefore, there is a need to prevent exposure,
which can be done by adopting a decentralized IS architecture model. A decentralized model is
reliable as the connection to the network is maintained when there is an issue with part of the
network or server. Indeed, it guaranteed continuous service delivery and support of the system.
Additionally, a decentralized system can act as a strategy for attracting and retaining
regular bike users. Such is through an increased sense of data safety and privacy, increasing
users’ trust. Guo et al. (2018) explain that centralized bike-sharing systems are characterized by
data processing problems when a computing mode is paralyzed. In turn, it paralyzes the system,
eventually leading to the loss of users. Based on the downsides of the centralized bike-sharing
systems, a decentralized model must be adopted to help avoid the problems and guarantee
success. Furthermore, decentralized models are best suited as they utilize blockchain technology.
The technology adds various benefits, including real-time monitoring of users’ data and
transactions (Guo et al., 2018). Notably, it will significantly improve the experience when paying
bills once the user returns the bike to the docking station. The real-time feature also ensures that
5
the organization can track the bikes’ location at all times. Such is beneficial in cases where users
unlock a bike in one station and return it in another.
Blockchain technology’s decentralized systems help trace parties that divulge user data.
Such is vital in removing them from the bike-sharing sharing system and taking legal action
when necessary. Also, the technology aids in recording real-time information that generates
credit scores. The credit scores awarded to users determine whether they should be allowed to
access bike-sharing services. Denying access to those with lower credit scores guarantees a
reduced loss of shared bicycles. In turn, it saves the organization from replacement costs incurred
after losses. The selected architecture is also beneficial because it supports distributed storage,
reducing pressure on a central server (Guo et al., 2018). Although a cloud model may function
similarly to a decentralized one, the number of users accessing the system at a given time may
not need the scale the cloud system offers. Moreover, a decentralized system is cheaper, saving
on the initial setup cost.
Software, Database, and Hardware Components
The bike-sharing system will require various software, database, and hardware
components to be fully functional. Notably, hardware comprises the physical features of the
system. They will include bikes, docking stations, racks, computers, servers, mobile devices,
GPS trackers, and network equipment like routers. The hardware will provide the framework of
the system, as the software components will significantly depend on it. The hardware
components must also meet the required specifications for efficiency. For instance, the correct
design of bike racks must be installed at the docking stations to ensure bikes fit in and the lock
mechanisms are functional. Similarly, computers and servers play an essential role in the bikesharing system. Therefore, the organization will have to conduct industry research to determine
6
the specifications best suited for the information system. It will ensure that informed decisions
are made during the purchase. Also, housing the hardware components is vital to protect them
from extreme weather conditions. For instance, the locks and racks need to be weather-sealed
and powder coated to increase durability.
The IS software components include the programs like the operating system, Mobile app,
tracking software, billing interface, and admin software at the docking stations. Both system and
application software will help accomplish all business processes and control activities. The
essential functions will involve user management, billing, file management, and memory
performance. The mobile app will be available on Appstore and google play store for users to
install. The app will be used in different processes, including user registration, unlocking bikes,
and billing. However, the docking stations will be equipped with admin software and computers
to serve customers without mobile devices when renting a bike. The tracking software will
monitor bicycle location and provide real-time rider information. Thus, the administrators will
know the bike locations and identify users who have not returned their rented bikes within the
stipulated timelines. In such a case, the admin software will automatically impose a fine on the
particular user. Bluetooth wireless connection aid in transmitting signals that activate the lock
mechanism in the docking station (Liu, 2018). For example, when a rider returns the bike and
checks out, a signal will be transmitted via Bluetooth, prompting the lock mechanism to secure
the bike at the rack. QR codes will also be available for locking and unlocking the bikes.
The database will also be central to the system’s business processes. As Vaishnavi (2020)
notes, a database is a collection of organized information that can be easily accessed, managed,
and updated. It can be accessed electronically from a computer system and is controlled by a
database management system. The group must conduct market research to determine the best
7
database type that suits the bike-sharing system. A database has five major components:
hardware, software, data, procedures, and database access language. The hardware consists of
electronic devices that provide storage and act as the interfaces between systems and computers.
On the other hand, software is a set of programs that control and manage the database. The
software will include DBMS, operating systems, network software, and application programs.
Network software is used to share data with other users, while the applications help access the
data. Data is the third component of the database. According to Vaishnavi (2020), it refers to
information that needs to be organized and processed. Therefore, database dictionaries are used
to centralize, document, control, and coordinate the use of data.
The procedure component of databases involves the instructions used in the management
of the system. Some of the instructions encompass setup and installation details, instructions to
log in and log out, manage operations, take data backups, and generate reports(Vaishnavi, 2020).
Notably, the procedures act as guidelines for the database administrators and provide new
administrators with a reference point. Its existence helps execute all processes effectively. The
last component of the database is the access language. It is used to write commands, access,
update, and delete stored data. The language allows administrators to execute various functions
within the system and display results in user-readable formats. For the bike-sharing system, the
database components will support business operations to give users better experiences and allow
optimal information system functioning.
The architecture of the System
Information system architecture represents business processes, procedures, data flow,
structure, and design. The hardware and software components of the system are also included in
8
its architecture. Notably, the system structure and interaction with various elements provide an
understanding of the system’s functionality.
Information Security Risks
Information Security (IS) risk is the dangers associated with the use, management, and
environments of information systems pose to an organization and its stakeholders. It overlaps
with many other types of risk when examining the possible consequences of a security-related
incident (McIlwraith, 2021). This essay discusses the main security risks, potential
consequences, strategies to minimize them, and a proposed information management structure.
Main Security Risks and their Potential Consequences
Human Error
Human error is one of the causes of unintentional or complacency data breaches when it
pertains to cybersecurity. It entails downloading malware, using a password that is not strong,
exposing one’s Internet Protocol (IP) address, and failing to update one’s software.
Malicious Insiders
A malicious insider is someone who purposefully abuses insider access to a company’s
proprietary information to damage the company’s reputation. This person could be a business
partner, a contractor, or an employee.
Phishing
Ransomware has quickly taken over as the primary method for cybercriminals to attack
organizations and infect them with malware. Phishing is one of the most common methods they
use. Attacks work by infecting a target organization with malware that spreads through its
systems, encrypts data, and compels the victim to stop using those systems for operations (Ikhsan
9
& Ramli, 2019). The thieves then send the organization a ransom demand, demanding payment
for the decryption key.
Strategies to Minimize Security Risks
Although it is in everyone’s nature to make mistakes, staff must be aware of the most
crucial information security components. All employees, technical or not, must get familiar with
the organization’s security rules and practices in the interim (McIlwraith, 2021). Doing this will
help reduce risks brought by human error. In addition, it is surprising to many businesses that
risks from negligent or malicious insiders are equally deadly and frequent as attacks from outside
the company. To stop a malicious insider from launching an attack, organizations need ways to
stop data breaches through email, identify questionable content in emails, and limit internal
communications.
Finally, multi-factor authentication, email spam filtering, and anti-virus software are
some of the methods to prevent phishing in an organization. It is crucial to regularly test the
recovery and restoration procedure built into one’s system and to have regular backups to support
one’s data. Also, training staff and employees on how to detect phishing scams is another way to
prevent falling prey to phishing scams.
Structure of Information Security Management
Information security teams may have different roles and duties depending on the
organization. An example layout of several functional roles and related duties in creating a
typical information security team structure is as follows. The senior management incorporates
various organizational roles like the Chief Information Security Officer (CISO) and includes
individuals in charge of overarching information security obligations. Information system
security experts are in charge of developing, implementing, overseeing, and reviewing the
10
organization’s security policies, standards, baselines, processes, and guidelines (Mirtsch, Kinne,
& Blind, 2020). Data owners are responsible for ensuring, among other things, that the
appropriate security—consistent with the company’s security policy—is incorporated into their
data systems. Also, the following responsibilities fall under the purview of IS auditors: Providing
independent assurance to management regarding the compatibility of the security objectives and
assessing the suitability and effectiveness of the security policy, standards, baselines, procedures,
and guidelines in order to meet the organization’s security objectives.
In conclusion, information system security plays an essential role in any organization.
Therefore, organizations should assess their vulnerability to security breaches and put up
measures to safeguard their data.
Data Modeling
The increasing use of modern applications has made data modeling a crucial aspect in
managing information. As record keeping becomes an integral part of all businesses, maintaining
and utilizing robust databases is becoming increasingly important. Various types of information,
ranging from contact and email information to sales and financial records, are stored in databases
(Liang, n.d.). Effective database design requires a well-designed data model that can organize
and manage this information in a consistent and organized manner. Data modeling is comparable
to an architectural plan, as it helps create conceptual models and connections between different
pieces of information (Yarlagadda, 2016). It is vital in creating a data framework as it visualizes
the design and relationships within the information, ensuring that the necessary data is structured
and organized appropriately.
Accordingly, an efficient bike-sharing system will require an Information System that can
support registration and billing processes. The paper focuses on designing data structures,
11
variables, lists, and objects that support the registration and billing processes for the system
project. Additionally, it analyzes the dependencies between the objects. The ultimate goal is to
provide a detailed analysis of the technical aspects of developing an Information System for
bike-sharing, particularly regarding the data architecture.
Designing Data Models for Registration and Billing Processes
Several data elements will be considered in designing data structures for the registration
and billing processes. For instance, the list of registered users and their current status will be
used to authenticate users during the login process and determine each user’s billing process. The
user’s status is equally important to indicate if a user has an active or inactive account. Another
critical data structure is the list of bikes available for rental. This list will include details such as
bike ID, bike type, location, and availability status. The bike ID will be used to identify each
bike and track its location through the system. The bike type and availability status will also be
essential in ensuring that users can find a bike that meets their needs and that the system can
track which bikes are available for rental. Additionally, data structures for the billing process will
include a list of transactions that record all bike rentals, fees charged, and user payments. This
data structure will track each user’s account balance, generate invoices, and maintain a record of
user payments. These data structures are elaborated on in the subsequent paragraphs.
However, when designing data structures for the bike-sharing system, it will be essential
to consider scalability. As the system grows and the number of users and bikes increases, the
data structures must accommodate the additional data. Therefore, the data structures will be
designed with consideration to scalability, using techniques such as Sharding to distribute data
across several servers. According to McKnight (2014), Sharding is a method of partitioning data
in a NoSQL system where each partition is stored on separate servers, potentially in different
12
locations, for improved performance. A single server manages one shard, which is usually
replicated twice on other shards. Sharding can either be automated or directed, with autosharding making it easier for programmers since it handles the data placement and retrieval
process (McKnight, 2014). In simple terms, it will help ensure scalability in the information
system by allowing horizontal scaling and improving the overall availability of the system.
Objects
Wilson (2022) posits that in object-oriented programming, a class is a blueprint that
defines the properties and behaviors of objects. Contrastingly, an object is an instance of a class,
meaning it is a specific representation of the class created from the class definition. Focusing on
specific data structures, objects with unique attributes, and connections to other elements will be
needed to ensure the smooth operation of the bike-sharing system. The first object will be the
user, who initiates the booking process using the mobile app. The app, which forms the second
object, provides details of bike locations, types, and rent costs. Once customers identify their
preferred bike, they have two options: pre-book or unlock at the docking station. The docking
station is the third object, which serves as the bike storage location. It has a unique identifier and
geographic location to aid in locating it on the app’s map. The bike is the fourth object in the
system.
Moreover, each bike has a unique identifier, location, and type, which helps users pick
their preferred bike besides having an available or unavailable status, which gets updated in realtime by the information system (IS). The IS system, which is the fifth object, plays a significant
role in the design, providing automated services such as pre-booking, bike unlocking, and realtime updates on the availability of bikes. The IS system stores data of all objects in the system,
and the bike data includes the bike type, status, location, and docking station. Finally, the rental
13
service is the sixth object, which calculates the rent costs based on the type of bike and the time
rented. It also provides a payment interface to pay for the rental.
Variables
In the project, various variables will be used to store user registration, billing, and bike
rental data. Wilson (2022) defines a variable as a storage area in a computer’s memory used to
hold values used within a program. It can be any data type, including string, number, or Boolean,
and can be changed or updated during the program execution. One of the essential variables is
user data, which will contain information such as the user’s name, address, phone number, email
address, and payment details. The user data will be used for account creation, billing, and
keeping track of the user’s rental history. Another variable is the bike data, which will contain
information about each bike, such as its unique identifier, location, and condition. This variable
will track bike availability and provide users with real-time information about the location of
bikes at different docking stations. Also, the system will have variables to store data related to
rental transactions, such as the date and time of the rental, the duration of the rental, and the
rental cost. These variables will be used to calculate the rental cost and generate invoices for
billing purposes.
In addition, variables will be used to store data related to bike maintenance, such as the
date of the last service and any repairs needed. This information will ensure that the bikes are in
good working condition and schedule regular maintenance. Other variables that will be used in
the project include location data, which will be used to track the location of users and bikes, and
booking data, which will contain information about pre-booked rentals. The system will use this
data to ensure that bikes are available at the requested location and to keep track of booking
cancellations and non-attenders (those who fail to show up). All of these variables will be
14
essential to the project, and their effective use will ensure the smooth functioning of the bike
rental system.
Lists
In addition to the objects and variables, lists will also play a crucial role in the successful
functioning of the bike-sharing application. The primary purpose of using lists is to store and
manage multiple data instances, for instance, the list of registered users, available bikes, and
docking stations. A list is a commonly used data structure in programming that allows for storing
a collection of items accessed by their index. The index of a list starts at 0, and each item in the
list has a unique index that identifies its position in the sequence (Wilson, 2022). They are
typically used to store homogeneous data, meaning all the elements in the list are of the same
data type, such as a list of integers, a list of strings, or a list of objects.
The system will maintain a list of registered users, including their names, email
addresses, phone numbers, and other relevant details. This list will enable the system to
authenticate and authorize users before providing access to the application’s functionalities.
Another critical list is the list of available bikes and their locations. This list will be updated in
real-time to provide users with accurate information about the location and availability of bikes.
Besides, it will store relevant information about the bikes, such as the type, model, and condition.
Similarly, the list of docking stations will be maintained, including information about each
station’s capacity, location, and availability. Also, lists will store information about the
transactions made by users. For instance, a list of all bike rentals made by each user will be
maintained to provide them with an overview of their rental history. This list will contain
information such as the rental start and end times, rental duration, rental cost, and the bike
15
rented. In a nutshell, through lists, the system can ensure the availability of bikes in docking
stations, authenticate, authorize users, and maintain a transaction history for each user.
Lists will be preferable for this project because they can dynamically adjust to changes in
the number of users or bikes. As the number of users and bikes in the system changes, the lists
will grow or shrink to accommodate the changes, making it easier to manage the data and ensure
the system remains efficient and effective. Besides, they facilitate easy indexing, especially when
searching for specific users or bikes. Also, lists are flexible and can store various data types, such
as strings, integers, and objects. Therefore, they will be useful when storing user information,
such as their name, address, and payment details, as well as bike information, such as the bike
type, availability, and location.
16
References
Guo, H., Zhou, J., Wang, J., & Wang, X. (2018). A Bike Sharing System Based on Blockchain
Platform. MATEC Web of Conferences, 232, 01027.
https://doi.org/10.1051/matecconf/201823201027
Ikhsan, M. G., & Ramli, K. (2019, June). Measuring the Information Security Awareness Level
of Government Employees Through Phishing Assessment. In 2019 34th International
Technical Conference on Circuits/Systems, Computers and Communications (ITCCSCC) (pp. 1-4). IEEE. https://doi.org/10.1109/ITC-CSCC.2019.8793292
Koksal, I. (2019, August 31). Why Businesses Need Diverse Payment Options. Forbes. Retrieved
January 12, 2023, from https://www.forbes.com/sites/ilkerkoksal/2019/08/31/whybusinesses-need-diverse-payment-options/?sh=6c08e1437ebd
Liang, Y. (n.d.). Data Modeling in System Analysis. University of Missouri–St. Louis. Retrieved
from https://www.umsl.edu/~sauterv/analysis/F2015/DataModeling.html.htm
Liu, L. (2018). Smart Control Components and Bicycle Sharing Systems. Proceedings of the
2018 8th International Conference on Manufacturing Science and Engineering (ICMSE
2018), 164, 667–670. https://doi.org/10.2991/icmse-18.2018.122
McIlwraith, A. (2021). Information Security and Employee Behaviour: How to Reduce Risk
Through Employee Education, Training and Awareness. Routledge.
https://doi.org/10.4324/9780429281785
McKnight, W. (2014). Operational Big Data. Information Management, 97–109.
https://doi.org/10.1016/b978-0-12-408056-0.00010-2
Mirtsch, M., Kinne, J., & Blind, K. (2020). Exploring the Adoption of the International
Information Security Management System Standard ISO/IEC 27001: A Web Mining-
17
Based Analysis. IEEE Transactions on Engineering Management, 68(1), 87-100.
https://doi.org/10.1109/TEM.2020.2977815
Nasr, L., Burton, J., Gruber, T., & Kitshoff, J. (2014). Exploring the Impact of Customer
Feedback on the Well-being of Service Entities. Journal of Service Management, 25(4),
531–555. https://doi.org/10.1108/josm-01-2014-0022
Vaishnavi, M. R. (2020). What is a Database? Know the Definition, Types & Components.
Edureka. Retrieved January 20, 2023, from https://www.edureka.co/blog/what-is-adatabase/
Wilson, K. (2022). Object-oriented programming. The Absolute Beginner’s Guide to Python
Programming, 109–120. https://doi.org/10.1007/978-1-4842-8716-3_9
Yarlagadda, R. (2016). Data Models in Information Technology. International Journal of
Innovations in Engineering Research and Technology [Ijiert], 3(2).
Purchase answer to see full
attachment
